Forum Discussion
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.
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 LearnHere 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
3 Replies
- TomMartens
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 LearnHere 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
- 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")) Thank you for the links and explanation. That helped me alot to understand where the problem is.
