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

Halleri's avatar
Halleri
Frequent Visitor
9 years ago

This was very helpful and applicable on a lot of things I'm currently working on. But as of what I understand, it's not applicable to limit the date slicer?

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

Nope. Nevertheless, you have some other nice options.

 

For example, you can create a calculated column in your date table with code like this:

 

IsPast = Date[Date] <= MAX ( Fact[Date] )

or

IsPast = Date[Date] <= TODAY ()

 

Then, in the report you can apply a single filter for IsPast=TRUE, so that any date in the future will not show up. Being a calcuated column, it is recomputed at every data refresh, so you do not need to modify the report filter to show new dates when facts appear for that date.

 


Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com