Forum Discussion

sgeheeb's avatar
sgeheeb
Icon for Resolver I rankResolver I
6 years ago

Trailing 12 Months issue

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...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

 

  • sgeheeb's avatar
    sgeheeb
    Icon for Resolver I rankResolver I
    6 years ago

    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)