Last 12 Months Sales

[AKARSEVGI]

How can I calculate the sales as sliding? I mean for example; we are in May 2022 . If I want to sum of sales by sales origin and sales department and country group but as last 12 months which doesn't include May 2022. How can I ?

[AKARSEVGI]

This very complicated. not just April. I want for all months.  

[Whitewater100]

Hello:

Are you able to check the solution for you? If you have any questions, i'll try to answer. Thanks

[AKARSEVGI]

Hello, Could you send it to me (sevgi.akar@haus.com.tr) if you have any example power bi work? I can study with it easly.  Thanks 

[Whitewater100]

Hi:

DAX can look complicated - I agree! But these measures aren't too bad after you use them a bit. I will attach a file which shows how the date table has relationship with the data table. If you look at the calc cols in Date Table you will see about insuring month is complete. It's critical to have this date table for time intel calculations. These figures are not April only. I hope this solves your question. Thanks

https://drive.google.com/file/d/11TnA0izWB65WgnsuJeDiGeuYrUN60gOG/view?usp=sharing 

[Whitewater100 · Accepted solution]

Hi:

You can have month slicer and choose April 2022:

Sales (last n months) =

VAR MaxFactDate =

    CALCULATE ( MAX ( Sales[Posting Date] ), ALL ( Dates ) ) // ignore the selected date filter, and find the max of date in Sales table

VAR FDate =

    ENDOFMONTH ( Dates[Date] ) // get the last day of the month selected in the date filter

VAR Edate =

    EDATE ( FDate,- [N Value] ) //  get the last day of -N months

RETURN

    IF (

MaxFactDate <= MAX ( Dates[Date] )

&& MaxFactDate > Edate,

        CALCULATE ( [Sales] ), ALL ( Dates ) )

You can also add Calc col to your date table and set filter to TRUE. This insures only completed months.

Completed Month =

VAR ThisMonthID =

LOOKUPVALUE('Dates'[Running Month Index], 'Dates'[Date Key], TODAY())

RETURN 'Dates'[Running Month Index] < ThisMonthID

//returns TRUE() if the month is in the past and complete

Some like using the folowing measure for past year:

CALCULATE([Total Sales],PARALLELPERIOD('Dates'[Date],-12,MONTH))

You can have filter for completed month here too:

I hope this solves for you.