1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Running Total of 12 previous months excluding current month

Discussion in 'Power Pivot, Power Map etc' started by Shay A, Sep 11, 2018.

  1. Shay A

    Shay A Member

    Messages:
    193
    Hi,

    I am trying to build a DAX expression to help me check, for each of the following months- 10/2017 through 09/2018 if there was sales ofat least one USD in the previos 12 months. For example, for 10/2017, the expression checks month 10/2016 till 09/2017. Also, the the formula shold check if there are sales on the month evaluated (10/2017 in this example).

    TY!
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,935
    As always, it would help us in helping you, if you upload sample workbook with data and manually created desired result.
  3. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    781
    First of all, do you have a calendar table in your data model, and does it contain a column with MonthRef like mm/yyyy-format that allows you to link the sales data with the calendar?
    If so you can more easily use a real date filter on you sales table in the required measurement.
  4. Shay A

    Shay A Member

    Messages:
    193
    I have only one table that I added to the Data Modal, and yes, it has a date column.

    Thanks
  5. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,935
    Hmm, without sample hard to give you exact answer.

    Depending on what date ranges are present in your data set, calculation changes a bit.

    When you have date dimension table (DimDate), you could use following construct.
    Code (vb):
    RSUM12M := CALCULATE (
        [Value],
        DATESBETWEEN (
            DimDate[Date],
            SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE ( DimDate[Date] ) ) ),
            LASTDATE ( DimDate[Date] )
        )
    )
    Full explanation of construct can be found in link below.
    https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

Share This Page