Forum Discussion
cumulative sum with empty results
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
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"))