Forum Discussion

Halleri's avatar
Halleri
Frequent Visitor
9 years ago
Solved

Rolling 12m for each date

Hi, I got a date table [DateTable] and a table of sales [Sales]. I've successfullt created a rolling 12m for sales:

RoSales 12m =
VAR minDate =
    DATE ( YEAR ( MAX ( DateTable[Date] ) ); MONTH ( MAX ( DateTable[Date] ) ) - 12; DAY ( MAX ( DateTable[Date] ) ) )
RETURN
    CALCULATE (
        SUM ( Sales[Sales] );
        FILTER (
            DateTable;
            DateTable[Date] > minDate
                && DateTable[Date] <= MAX ( DateTable[Date] )
        )
    )

What I want to do, and failed repeatedly to, is to get a chart with dates on the X-axis and the Sales for the last 12 months at each date on the Y-axis.
Example if in table: 

 

 

|____Date_______|_Rolling 12__|
| 10 Aug 2017    | 100K           |
| 11 Aug 2017    | 101K           |
| 12 Aug 2017    | 108K           |
| 13 Aug 2017    | 107K           |

 

All my attempts results in the measure taking in the context of the date and plots out the sum for that date, when I really want the sum of the whole last year with the date as end point. Is this possible?

Cheers!

 

Need Help
  • AlbertoFerrari's avatar
    AlbertoFerrari
    9 years ago

    Why do you have that additional reference to the Date extension? The formula should look like this:

    test 12m =
CALCULATE (
    SUM ( Sales[SalesAmount] );
    DATESINPERIOD (
        DateTable[Date];
        CALCULATE ( MAX ( DateTable[Date] ) );
        -1;
        YEAR
    )
)

    Notice the missing .[Date] in the first parameter of DATESINPERIOD (DateTable[Date]).

     

    The extension created by Power BI contains the full year. My advice is always the same with auto date/time: disable it, learn time intelligence and forget about its existence :)

     

    Anyway, removing that reference should fix the problem although a date table ending in August 26 is not a best practice, the best would be to protect your code using an IF statement that blanks the measure. With that said, in your special case, you can live with an incomplete date table.


    Have fun with DAX!

    Alberto Ferrari
    http://www.sqlbi.com

     

    Alberto

10 Replies

  • AlbertoFerrari's avatar
    AlbertoFerrari
    Icon for Most Valuable Professional rankMost Valuable Professional
    9 years ago

    Yep, a MAX would solve the issue. Moreover, you can make it much easier relying on time intelligence functions, like this:

     

    CALCULATE (
    SUM ( Sales[SalesAmount] ),
    DATESINPERIOD ( 'Date'[Date], LASTDATE ( 'Date'[Date] ), -1, YEAR )
)

    Your problem is the relationship, ALL makes DAX ignore it, DATESINPERIOD does it automatically.


    Have fun with DAX!

    Alberto Ferrari
    http://www.sqlbi.com

    • Halleri's avatar
      Halleri
      Frequent Visitor
      9 years ago

      Hi Alberto, 

       

      Thank you very much for your answer!
      I used your formula with a little modification.

      test 12m =
CALCULATE (
    SUM ( Sales[SalesAmount] );
    DATESINPERIOD (
        DateTable[Date].[Date];
        CALCULATE ( MAX ( DateTable[Date] ) );
        -1;
        YEAR
    )
)

      And I'm very close to the desired result:

      My [DateTable] looks like this:

      DateTable =
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Sales'[Calendar day] ); MAX ( 'Sales'[Calendar day] ) );
    "DateId"; FORMAT ( [Date]; "YYYYMMDD" );
    "Year #"; YEAR ( [Date] );
    "Year/Month"; FORMAT ( [Date]; "YYYY/MM" );
    "Month #"; FORMAT ( [Date]; "MM" );
    "Month name short"; FORMAT ( [Date]; "mmm" );
    "Month name long"; FORMAT ( [Date]; "mmmm" );
    "Week #"; WEEKNUM ( [Date] );
    "DayOfWeek #"; WEEKDAY ( [Date] );
    "Day #"; FORMAT ( [Date]; "dd" );
    "Day name"; FORMAT ( [Date]; "dddd" );
    "Quarter"; FORMAT ( [Date]; "Q" )
)


      The remaining problem is that the chart plots out dates beyond Aug 26, 2017. The DateTable only contains dates up to that date, and so does [Sales].

      What is the cause of this? And how can I fix it?

      • AlbertoFerrari's avatar
        AlbertoFerrari
        Icon for Most Valuable Professional rankMost Valuable Professional
        9 years ago

        Why do you have that additional reference to the Date extension? The formula should look like this:

        test 12m =
CALCULATE (
    SUM ( Sales[SalesAmount] );
    DATESINPERIOD (
        DateTable[Date];
        CALCULATE ( MAX ( DateTable[Date] ) );
        -1;
        YEAR
    )
)

        Notice the missing .[Date] in the first parameter of DATESINPERIOD (DateTable[Date]).

         

        The extension created by Power BI contains the full year. My advice is always the same with auto date/time: disable it, learn time intelligence and forget about its existence :)

         

        Anyway, removing that reference should fix the problem although a date table ending in August 26 is not a best practice, the best would be to protect your code using an IF statement that blanks the measure. With that said, in your special case, you can live with an incomplete date table.


        Have fun with DAX!

        Alberto Ferrari
        http://www.sqlbi.com

         

        Alberto

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Perhaps try an ALL(DateTable) or something like that to specify the context within the measure.

    • Halleri's avatar
      Halleri
      Frequent Visitor
      9 years ago

      I've tried several solutions where I add ALL(DateTable)/ALL(DateTable[Date])/ALL(DateTable[Date].[Date] and so on, without any success.
      In my measure above I specify the date range which references the slicer. The date in the formula need to reference the date point in the chart rather than the measure. So in some way ignore the context of sales for the date in the chart, and instead add context between the formulas date range and the date in the chart.

      Does my logic compute? :smileytongue: