topic Re: 12 Previous Months Sum for each month in the past from current month in DAX Commands and Tips

12 Previous Months Sum for each month in the past from current month

Midway — Thu, 03 Oct 2019 01:40:45 GMT

Hi there,

Been trying to get the DAX for this but no luck so far. Need some help on the following.

So for each month, I'm trying to Sum the previous 12 months of data. Let's say for September 2019, I need to go back 12 months and sum the number of sales (Oct/2018 to Sept/2019). The same goes for the rest of the months in the past, for Aug/2019 should be Sept/2018 to Aug/2019 and so on for the rest of the months in the past.

Thanks for your help!

Midway

Re: 12 Previous Months Sum for each month in the past from current month

kentyler — Thu, 03 Oct 2019 02:39:50 GMT

here's some sample code i found online

Sales12M := CALCULATE (
    [Sales],
    DATESBETWEEN (
        Calendar[FullDate],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( Calendar[FullDate] ) ) ),
        LASTDATE ( Calendar[FullDate] )
    )
)

Re: 12 Previous Months Sum for each month in the past from current month

Midway — Thu, 03 Oct 2019 17:01:26 GMT

Thanks for the reply there, but don't think this is giving me what I need. What would be the Calendar FullDate?

I do have a calendar table tho.

Re: 12 Previous Months Sum for each month in the past from current month

kentyler — Thu, 03 Oct 2019 17:05:27 GMT

That would be the name of the field in your calendar table that holds the date.

Re: 12 Previous Months Sum for each month in the past from current month

Midway — Thu, 03 Oct 2019 17:34:24 GMT

That's what I thought, but still not showing the right numbers. This is suming the last 12 months from current month (October 2019 to October 2018) but this is just repeating the same numbers for the other dates. Like for example Sep-2019 should sum from Sept-2019 to Sept 2018 and so forth.

Re: 12 Previous Months Sum for each month in the past from current month

kentyler — Thu, 03 Oct 2019 17:33:43 GMT

Can you make a sample pofwer bi file available ?

Re: 12 Previous Months Sum for each month in the past from current month

Midway — Thu, 03 Oct 2019 17:46:03 GMT

This is an example of what I'm trying to accomplish. The previous 12 months sum column should sum from current month 12 months in the pat. For the previous month, it should sum from Septeber 2019 to past 12 months and so on for the rest of the months in the past. Let me know if I'm making any sense here please.

Sale Date	Sales	Previous 12 months sum
Jun-18	20
Jul-18	18
Aug-18	30
Sep-18	20
Oct-18	22
Nov-18	40
Dec-18	60
Jan-19	21
Feb-19	19
Mar-19	20
Apr-19	56
May-19	20	346
Jun-19	78	404
Jul-19	12	398
Aug-19	44	412
Sep-19	11	403
Oct-19	60	441

Re: 12 Previous Months Sum for each month in the past from current month

kentyler — Thu, 03 Oct 2019 19:51:12 GMT

I added some months to make it easier for me to debug the measure

here is the measure:

Prev 12 Months Sales =

VAR start_date = max('Sales'[Sales Date])

var start_date_for_sales_month = datevalue(month(start_date) & "/1/" & year(start_date))

var end_date_for_month = edate(start_date_for_sales_month,-12)

var min_date = calculate(min(Sales[Sales Date]) ,All())

var min_date_month = datevalue(month(min_date) & "/1/" & year(min_date))

var show_amount = min_date_month <= end_date_for_month

var amount = calculate(sum(sales[sales amount]),sales[sales date] <= start_date_for_sales_month, sales[sales date] <= start_date)

return if(show_amount,amount)

There might be easier ways.

I started by getting the first day of the month for the row.

Then i used edate to get a date 12 months in the past

I calculated the minimum date in the data set

then i checked to see if the month 12 months in the past was greater than the minimum date in the dataset and less than the month in the current row

if it was, then i used calculate to add up the sales for the month.

DAX has some great functions to simplify calculating periods, and dates in periods, but i could not figure out how to use them when the month was represented by "any date in the month"

Hope this helps

Ken Tyler