Trailing 12 Months issue

[sgeheeb]

Hi,

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.

First of all, I am struggleing with the measure: The following should be working, but produces the wrong results:

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
        )
    )

If a use a measure, which simply calcualtes the trailing twelve month from the current date I still run into problems:

The measure:

12 M (incl. today) =
CALCULATE (
    [Sales Amount],
    DATESINPERIOD (
        dSalesOrderInsertedDate[Sales_Order_Insert Date],
        LASTDATE ( dSalesOrderInsertedDate[Sales_Order_Insert Date] ),
        -1,
        YEAR
    )
)

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.

If I use another date slicer to select a month (e.g. July), the line-chart only displays the selected month:

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)

Any help on the measure or a method to display the correct timespan would be greatly appreciated.

[amitchandak]

@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))  

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.

[sgeheeb]

Hello,

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. 

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)