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

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

tate's avatar
tate
Frequent Visitor
6 years ago

Hi! 

 

An old thread but has something changed or would you be able to explain the below.

This gives me 2019-11-19:
Last Date in the Period for DatesInPeriod =
LASTDATE(
DATESINPERIOD(Dim_Date[Date];
LASTDATE(Dim_Date[Date]);-1;YEAR)
)

 

but why I get 2018-12-01 from the below and not 2018-11-20:

First Date in the Period for DatesInPeriod =

FIRSTDATE(
DATESINPERIOD(Dim_Date[Date];
LASTDATE(Dim_Date[Date]);-1;YEAR)
)
 
The below gives 2018-11-20 as expected. 

First Date in the Period for DatesInPeriod =

FIRSTDATE(
DATESINPERIOD(Dim_Date[Date];
LASTDATE(Dim_Date[Date]);-365;DAY)
)
 
I'm confused and because of this the previously made Rolling 12m calculation doesn't seem to work and I have used another way to calculate it which works:
Sales 12m= CALCULATE (
Sum(Sales);
DATESBETWEEN (
Dim_Date[Date];
NEXTDAY ( DATEADD( LASTDATE(Dim_Date[Date]);-1;YEAR));
LASTDATE ( Dim_Date[Date] )
)
)
 
I have marked the date table as calendar table in the model and I'm using Version: 2.75.5649.582 64-bit. I'm afraid I will need to make quite a many changes if I will need to make the same changes to all the rolling 12 months calculations in different models.