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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
BI_Samurai
Frequent Visitor

Rolling cumulative TOTAL with 2 calendars

Hello,

 

I would like to calculate correctly a total of a sales measure to show a rolling cumulative total for the last 12 months.

 

This measure is intented to work with 2 calendars - first main calendar is used to select data in the slicer and second calendar is used to display data on the visual only until the selected month from the first calendar and slicer.

 

The goal is to display the data only until the time period selected in the year/month slicers meaning that if I select April 2024 I should see the total result calculating May 2023 up to April 2024. For a single month I get correct values, however the totals at the moment return only data relevant to 2024.

 

This is the measure:

 

Sales Rolling test =

var var_end =
MAX('📆Calendar 2'[Date])

var var_start = EOMONTH(var_end,-12)

var var_result =

IF(SELECTEDVALUE('📆Calendar 2'[Month #])>SELECTEDVALUE('📆Calendar'[Month #]),
blank(),
CALCULATE(SUM(Sales[Total Sales]),
    REMOVEFILTERS('📆Calendar'),
   
 KEEPFILTERS('📆Calendar'[Date] > var_start &&'📆Calendar'[Date]<=var_end)))


RETURN
var_result
 
BI_Samurai_0-1712205785307.png

Do you know what needs to be added to my measure in order to solve this?

Any other ideas how to fix this?

 

Thank you.

1 ACCEPTED SOLUTION

It's a rolling 12 Month Measure. 

 

Sales - Last 12 Months = 
CALCULATE (
    [Sales],
    DATESINPERIOD ( Calendar[Date], MAX ( Calendar[Date] ), -12, MONTH )
)

Sales is a simple Sum

SUM(Sales[Amount])






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

Proud to be a Super User!





Learn about the Star Schema, it will solve many issues in Power BI!

Date tables help! Learn more



LinkedIn
Let's connect on LinkedIn


View solution in original post

4 REPLIES 4
Joe_Barry
Super User
Super User

Hi @BI_Samurai 

 

Create an Inactive relationship between Calendar 2 and Calendar on the Date columns. Create a measure similar to below. Add the Date columns in the visual from Calendar2 and the Slicer data should come from Calendar.

Sales P12M = 
VAR RefDate =
    MAX ( Calendar[Date] )
VAR PrevDate =
    DATESINPERIOD ( Calendar2[Date], RefDate, -12, MONTH )
RETURN
    CALCULATE (
        [Sales - Last 12 Months],
        REMOVEFILTERS ( Calendar ),
        KEEPFILTERS ( PrevDate ),
        USERELATIONSHIP ( Calendar[Date], Calendar2[Date] )
    )

 

 

Hope this helps

Joe

 

If you found my answer helpful and it solved your issue, please accept as solution




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

Proud to be a Super User!





Learn about the Star Schema, it will solve many issues in Power BI!

Date tables help! Learn more



LinkedIn
Let's connect on LinkedIn


Awesome, thank you !

Hello,

 

thanks for a quick reply and the tip. What is the exact measure that you use here [Sales - Last 12 Months] ?

thank you

It's a rolling 12 Month Measure. 

 

Sales - Last 12 Months = 
CALCULATE (
    [Sales],
    DATESINPERIOD ( Calendar[Date], MAX ( Calendar[Date] ), -12, MONTH )
)

Sales is a simple Sum

SUM(Sales[Amount])






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

Proud to be a Super User!





Learn about the Star Schema, it will solve many issues in Power BI!

Date tables help! Learn more



LinkedIn
Let's connect on LinkedIn


Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors