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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
joemillson
Frequent Visitor

Cumulative total - stopping after last date not working with hierarchy

Hi - i've created a measure as shown here to create a cumulative sum up to the last sales date, then to give a blank after that date:

 

Cml Actual Invoices =
VAR LastSalesDate = CALCULATE(LASTDATE(f_scoro_SalesInvoicesActual[Date Invoice]),ALL())
RETURN
IF(
SELECTEDVALUE(d_time_Dates[Date]) > LastSalesDate, BLANK(),
CALCULATE([Sum of Actual Invoices],
FILTER(ALLSELECTED(d_time_Dates),d_time_Dates[Date] <= MAX(d_time_Dates[Date]))))
 
It shows correctly if my visual is operating at the single date level:
joemillson_0-1624792977434.png

However I need to be able to move up / down a date hierarchy and for the measure to work, which is currently is not doing, as shown below if i move up to Month granularity, the meaure no longer returns a blank after the last sale date, and repeats the cumulative total for months beyond the last date:

 

joemillson_1-1624793092067.png

I think I understand why, becuase in my measure I'm calculating based on the full date key and not the month, however I don't know the solution. Any help would be appreciated.

 

Thank you,

Joe

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@joemillson , This seems fine and should work in a hierarchy. Just make sure that all these Fiscal Year, Qtr, and Month are coming from date table, d_time_Dates and that is marked as date table

 

Try this too

Cml Actual Invoices =
VAR LastSalesDate = maxx(ALL(f_scoro_SalesInvoicesActual),f_scoro_SalesInvoicesActual[Date Invoice])
RETURN
IF(
max(d_time_Dates[Date]) > LastSalesDate, BLANK(),
CALCULATE([Sum of Actual Invoices],
FILTER(ALLSELECTED(d_time_Dates),d_time_Dates[Date] <= MAX(d_time_Dates[Date]))))

 

or

 

 

Cml Actual Invoices =
VAR LastSalesDate = maxx(ALL(f_scoro_SalesInvoicesActual),f_scoro_SalesInvoicesActual[Date Invoice])
RETURN
CALCULATE([Sum of Actual Invoices],
FILTER(ALLSELECTED(d_time_Dates),d_time_Dates[Date] <= MAX(d_time_Dates[Date]) && (d_time_Dates[Date]) <= LastSalesDate))

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

3 REPLIES 3
JoaoEccel
Regular Visitor

The first option works just as I needed. Thank you so much!

amitchandak
Super User
Super User

@joemillson , This seems fine and should work in a hierarchy. Just make sure that all these Fiscal Year, Qtr, and Month are coming from date table, d_time_Dates and that is marked as date table

 

Try this too

Cml Actual Invoices =
VAR LastSalesDate = maxx(ALL(f_scoro_SalesInvoicesActual),f_scoro_SalesInvoicesActual[Date Invoice])
RETURN
IF(
max(d_time_Dates[Date]) > LastSalesDate, BLANK(),
CALCULATE([Sum of Actual Invoices],
FILTER(ALLSELECTED(d_time_Dates),d_time_Dates[Date] <= MAX(d_time_Dates[Date]))))

 

or

 

 

Cml Actual Invoices =
VAR LastSalesDate = maxx(ALL(f_scoro_SalesInvoicesActual),f_scoro_SalesInvoicesActual[Date Invoice])
RETURN
CALCULATE([Sum of Actual Invoices],
FILTER(ALLSELECTED(d_time_Dates),d_time_Dates[Date] <= MAX(d_time_Dates[Date]) && (d_time_Dates[Date]) <= LastSalesDate))

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thanks very much - the first alternative works perfectly.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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