Trailing 12 Months issue

Question

Hi,&nbsp;I am trying to to build a line chart showing a company's sales over the prior 12 months, starting with the last full month. So today being 2nd July, the line chart would display data for June 2019 to June 2020.&nbsp;First of all, I am struggleing with the measure: The following should be working, but produces the wrong results:&nbsp;12 M =
VAR _EndDate =
    DATE ( YEAR ( LASTDATE ( dSalesOrderInsertedDate[Sales_Order_Insert Date] ) ), MONTH ( ( LASTDATE ( dSalesOrderInsertedDate[Sales_Order_Insert Date] ) ) ) - 1, 1 )
VAR _StartDate =
    DATE ( YEAR ( LASTDATE ( dSalesOrderInsertedDate[Sales_Order_Insert Date] ) ) - 1, MONTH ( ( LASTDATE ( dSalesOrderInsertedDate[Sales_Order_Insert Date] ) ) ) - 1, 1 )
RETURN
    CALCULATE (
        [Sales Amount],
        DATESBETWEEN (
            dSalesOrderInsertedDate[Sales_Order_Insert Date],
            _StartDate,
            _EndDate
        )
    )&nbsp;&nbsp;If a use a measure, which simply calcualtes the trailing twelve month from the current date I still run into problems:&nbsp;The measure:&nbsp;12 M (incl. today) =
CALCULATE (
    [Sales Amount],
    DATESINPERIOD (
        dSalesOrderInsertedDate[Sales_Order_Insert Date],
        LASTDATE ( dSalesOrderInsertedDate[Sales_Order_Insert Date] ),
        -1,
        YEAR
    )
)&nbsp;&nbsp;If I only select a Year on the date-slicer, things look like this :this shows the full year 2019, but from January to December. I need it to display July 2019 to July 2020.&nbsp;If I use another date slicer to select a month (e.g. July), the line-chart only displays the selected month:&nbsp;I get that this seems to be the default behaviour, but I need the chart to display July 2019 to July 2020 (or, ideally June 2019 to June 2020)&nbsp;Any help on the measure or a method to display the correct timespan would be greatly appreciated.

amitchandak · Answer

sgeheeb , Try something like this 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))  
&nbsp;
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-bihttps://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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184Appreciate your Kudos.
&nbsp;

sgeheeb · Answer

Hello,&nbsp;I am already using a date dimension ([dSalesOrderInsertedDate]). When I use your measures, I get the same result as with the others I have tried: The line chart keeps showing January - December if I select a year and no month. When I do chose a month, it only shows a dot, because the chart is being filtered down to the chosen month.&nbsp;&nbsp;What I want to do is chose a single date (e.g. today (02-07-2020)) and display the cumulated sales by month on a line chart starting at 01-06-2019 and ending at 30-06-2020)&nbsp;&nbsp;

Forum Discussion

Trailing 12 Months issue

2 Replies

Related content

12 months trailing measure

Trailing Month Slicer

Remove trailing commas

Trailing 12 months DAX

Fabric Free Trail