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 ( M...
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

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: