Rolling sum for last 3 months

[yodha]

Hi, i am trying to create rolling sum for 3 months, starting from current month to past 2 months.

i have tried this: 

Measure = CALCULATE([Cost %], DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH))

but it's not working. My fact table "Date" coumn has many dupliacte values,

   

and the relationship between fact table and date table is many to one,

How to change the DAX when there are duplicate values? Any help on this much appreciated.

Thank you.

[jairoaol]

the picture doesn't look good. very small.

the date table must NOT have duplicate values. must be marked as a date table.

in the formula you should keep in mind that it is assumed that you have applied the date filter where you define the date with which you will look for the 3 months backwards.

[yodha]

@jairoaol : Hi, there are no duplicates in my date table/calender table  "date" column but there are duplicates in my source table (table 1) date column (attached snap shot above), and yes am trying to calculate rolling sum from backwards, thank you.

[parry2k · Accepted solution]

@yodha not sure what is in your measure (cost %) but it should be -2 instead of -3

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[amitchandak]

@yodha , Date column should have duplicate dates. How you have created. Try to create with calendar function and mark it as date table. the formula is correct

example

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-3,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.

[yodha]

@amitchandak : Thanks for the response , i did the same, created date table using calender function, and what you mean "Date column should have duplicate dates?"... there are duplicates in my fact table date column.