Need filter in the Latest Date calc and not sure of the format

[lasmithfla]

This calc works to get last 12 months of sales IGNORING the calendar date filter.  I need the same type of calc now pulling the previous 12 months and not sure where the ALL filter can be added in the Var Latest date?

THis one works....

Sample date period would be lastest date would be 8/1/20.  This totals sales from 8/1/20 back through 9/1/19.

TotalSale12Months =

VAR LatestDate = LASTDATE ( ALL ( SalesSummary[Data Date] ) )

RETURN //LatestDate --Test variable result CALCULATE ( [Total Sales], ALL ( 'Calendar'[PKDate], 'Calendar'[PKDate] ), DATESINPERIOD ( SalesSummary[Data Date], LatestDate, -12, MONTH )

This is the one I need help with - basically need this calc to start LAST DATE -12 months, then Sum data 12 months prior to that.

So the starting point for this one using sample dates would be - I need my latest date to be 8/1/19 (12 months prior to max data date).  Then the 2nd part of the calculate, sums up 12 months prior to that.

The calc works until I change my date filter, I need to include the ALL in the lastest date part so it ignores my calendar filter and

can't get it right.

TotalSale12Months =

VAR LatestDate = LASTDATE ( dateadd( SalesSummary[Data Date],-12,Month ) )

RETURN //LatestDate --Test variable result CALCULATE ( [Total Sales], ALL ( 'Calendar'[PKDate], 'Calendar'[PKDate] ), DATESINPERIOD ( SalesSummary[Data Date], LatestDate, -12, MONTH )

[v-deddai1-msft]

Hi @lasmithfla ,

Just try the following measure:

TotalSale12Months =
VAR LatestDate =
    LASTDATE ( DATEADD ( ALL ( SalesSummary[Data Date] ), -12, MONTH ) )
RETURN
    CALCULATE (
        [Total Sales],
        ALL ( 'Calendar'[PKDate] ),
        DATESINPERIOD ( SalesSummary[Data Date], LatestDate, -12, MONTH )
    )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[amitchandak · Accepted solution]

@lasmithfla , Not very clear

See if this can work with a date table

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-12,MONTH))
Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-12,month)),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.