cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
theDarkPrince
Helper II
Helper II

Cumulative Total shows only for starting few months

Hi Everyone, this one's got me scratching my head for a while now. A lot of searching but could not understand the problems' root cause or how to fix it. If you refer to screenshot below, you'll notice that it's showing running total only till the month of September (current month). I have brought in record_type into the legend well and the measure I have for values well has definition as below:

Cumulative OI = 
var Result =
IF( MIN('Dates'[Date]) <= TODAY(), TOTALYTD( SUM( 'Source Tbl'[amount] ), 'Dates'[DATE], "31/03" ), BLANK() )
Return Result

 

I have similar setup (relationships in Data Model, columns used etc.) in another report for a different use case and for that it works perfectly fine. Only difference : instead of using record_type as legend, I have used FinancialYear from Dates table. But over there it shows running totals as expected i.e. line for Actuals will be only shown till current month but for others it shows all months from April through March. Any ideas about what might be causing this ? Any help is appreciated. Thanks in advance.

 

theDarkPrince_0-1630996789221.png

 

3 REPLIES 3
Eyelyn9
Community Support
Community Support

Hi @theDarkPrince ,

 

The pictures you provided are not available...

Eyelyn9_0-1631691006295.png

 

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

 

Best Regards,
Eyelyn Qin

amitchandak
Super User
Super User

@theDarkPrince , Try like

TOTALYTD( SUM( 'Source Tbl'[amount] ), 'Dates'[DATE],'Dates'[Date] <= TODAY(), "31/03" )

 

or

TOTALYTD( SUM( 'Source Tbl'[amount] ), filter('Dates', 'Dates'[DATE],'Dates'[Date] <= TODAY()), "31/03" )

 

as date is nor in context min date is start of yesr



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

Hi @amitchandak . Thank you for responding. I tried the first one you suggested however, lines other than ACTUALS flatten from current month i.e. they show the same values, which should not be the case. For other lines (record types), I do have data so it should ideally add up, right ? Please find screenshots below. They might help identify the problem.

 

theDarkPrince_0-1630999859351.png

 

theDarkPrince_1-1630999902642.png

 

The second definition that you suggested, seemed to contain syntax error. So I tweaked it as below :

 

var Result = TOTALYTD( SUM( 'Src Tbl'[amount] ), FILTER('Dates', 'Dates'[Date] <= TODAY()), "31/03" )

 

But even after removing the syntax error, I get another error as "Column 'RowNumber-2662979B-1795-4F74-8F37-6A1BA8059B61' in table 'Dates' cannot be found or may not be used in this expression."

 

Again, thanks for taking out the time to read through the post and respond. 🤗

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.