cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## cumulative sum with empty results

I have a meassure which calculates the cumulative sum for booking periods.

= CALCULATE (
SUM(data[transaction_amount]);
FILTER (
ALL (data[Booking_period]);
data[Booking_period] <= MAX (data[Booking_period])))

The numbers are correct but if a booking period does not have data entries for a specific booking period the cells remain bank.

Can somebody explain to me what is going on here? And how can I fix it so that instead of  empty cells the values from the previous booking period are shown.

1 ACCEPTED SOLUTION
Super User

Hey @Michael-N-N ,

you are facing the auto-exist problem, that is described in this article. in more detail: Understanding DAX Auto-Exist - SQLBI

To solve this issue you have to create a star schema, this article describes the importance of a star schema: Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

Here you will find guidance on how to create a date table and how to create running sum measures: Time patterns – DAX Patterns

Make sure that you are using the month column from the date table in your visuals, after you created the date table, the relationships, and after adapting your existing measure.

Hopefully, this will you to tackle your challenge.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
3 REPLIES 3
New Member

Thank you for the links and explanation. That helped me alot to understand where the problem is.

Super User

Hi,

Assuming you have a proper Date column in the Data Table, create a Calendar Table.  Create calculated column formulas of Year, Month name and Month number.  Sort the Month name column by the Month number column.  Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.  To your visual, drag Month name from the Calendar Table.  In a slicer drag the year from the Calnedar Table and select a year there.  Write this measure to get the YTD total.  I have assumed that 31/12 is the end of t he FY.

Measure = CALCULATE(SUM(data[transaction_amount]);datesytd('calendar'[date],"31/12"))

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Hey @Michael-N-N ,

you are facing the auto-exist problem, that is described in this article. in more detail: Understanding DAX Auto-Exist - SQLBI

To solve this issue you have to create a star schema, this article describes the importance of a star schema: Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

Here you will find guidance on how to create a date table and how to create running sum measures: Time patterns – DAX Patterns

Make sure that you are using the month column from the date table in your visuals, after you created the date table, the relationships, and after adapting your existing measure.

Hopefully, this will you to tackle your challenge.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors