Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mbz0319
Helper I
Helper I

Rolling 13 Months Sum DAX

Hi friends,

 

I am trying to create Rolling 13 months sum using DAX and I am looking for some help around it.

 

This is my data set. And I want to create a measure and not a column which gives me Rolling 13 months sale for the month I select in the filter.

Data Set:

 

Month YearSale
Apr-211000
May-21200
Jun-213000
Jul-211200
Aug-211500
Sep-211600
Oct-211300
Nov-212000
Dec-211000
Jan-222000
Feb-223000
Mar-221000
Apr-221000
May-222000
Jun-221000

 

Result Expected:

 

Month YearRolling 13 Month Sale
Apr-2219800
May-2220800
Jun-2221600

 

Thanks

2 ACCEPTED SOLUTIONS
danextian
Super User
Super User

Hi @mbz0319 ,

 

This measure below will work if you have a date column in your table. It won't work if you have only the month name as it is a text.

Measure = 
CALCULATE(
    SUM( 'Table'[Sale] ),
    DATESINPERIOD( 'Table'[DateRef], MAX( 'Table'[DateRef] ), -13,  MONTH )
)

 

 

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks Ashish for your help and time.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
danextian
Super User
Super User

Hi @mbz0319 ,

 

This measure below will work if you have a date column in your table. It won't work if you have only the month name as it is a text.

Measure = 
CALCULATE(
    SUM( 'Table'[Sale] ),
    DATESINPERIOD( 'Table'[DateRef], MAX( 'Table'[DateRef] ), -13,  MONTH )
)

 

 

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Hi mate, thans for your time and help, it worked.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.