cumulative sum with empty results

[Michael-N-N]

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.

[Michael-N-N]

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

[Ashish_Mathur]

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"))

[TomMartens · Accepted solution]

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