Forum Discussion
How to modify some DAX measures to solve a Running Total problem
- 1 year ago
hcova7
Please try with this measure:RTotal_6 := VAR _CurrentDate = MAX( 'Calendar'[Date] ) VAR _StartDate = CALCULATE( MIN( 'Calendar'[Date] ); ALLSELECTED( 'Calendar' ) ) VAR _EndDate = CALCULATE( MAX( 'Calendar'[Date] ); ALLSELECTED( 'Calendar' ) ) VAR _Range = DATESBETWEEN( 'Calendar'[Date]; _StartDate; _CurrentDate ) VAR _RunningTotal = CALCULATE( SUMX( System; 'System'[ProfitUSD] ); _Range ) RETURN _RunningTotalI hope this help, if so please mark as a solution. Kudos are welcome😀
Hi there.
I found a solution to my problem. Near to perfect.
The solution works perfect and it generates perfect values too. However I believe that I am doing a mistake in the way I am coding the DAX measure.
For my Yearly RTotal (a YTD version) I wrote the following code in the System table
Yearly RTotal =
=CALCULATE( [Total], DATESYTD( 'Calendar'[Date] ) )
where Total is:
Total =SUM(System[ProfitUSD])
For my All-Time RTotal I wrote the following code in the Calendar table
All-Time RTotal =
VAR
CurrentDate = MAX('Calendar'[Date])
VAR
StartDate = CALCULATE(MIN('Calendar'[Date]), ALL('System'))
VAR
EndDate = CALCULATE(MAX('Calendar'[Date]), ALLSELECTED('Calendar'))
VAR
RunningTotal =
CALCULATE(
SUM('System'[ProfitUSD]),
FILTER(
ALL('Calendar'),
'Calendar'[Date] <= CurrentDate
)
)
RETURN
IF(
CurrentDate < StartDate || CurrentDate > EndDate,
BLANK(), -- Return BLANK() for dates outside the timeline bounds
COALESCE(RunningTotal, 0)
)
The problem, and here where I beleive that problem arises, is that I need to insert 2 timelines to work properly.
Let me clarify this with the below pivot table.
In the above picture "Date" timeline comes from "Calendar" table and it takes control over the Pivot table "Year" column, filtering dates from 2011-2014. "Date/Time_3" timeline comes from "System" table and take control of the Yearly and All-time RTotal. In other words, if I delete the "Date" timeline I will get wrong results, as shown below. (wrong calculations in column "All-Time RTotal")
Conversely, if I delete "Data/Time_3" timeline, I get the following pivot table:
As you can realize, it works wrong too.
Finally, can anybody let me know how to modify the above shown measure so I can work with one Timeline only instead of 2?
Here is the Excel file link
- pcoley1 year ago
Super User
hcova7
Please try with this measure:RTotal_6 := VAR _CurrentDate = MAX( 'Calendar'[Date] ) VAR _StartDate = CALCULATE( MIN( 'Calendar'[Date] ); ALLSELECTED( 'Calendar' ) ) VAR _EndDate = CALCULATE( MAX( 'Calendar'[Date] ); ALLSELECTED( 'Calendar' ) ) VAR _Range = DATESBETWEEN( 'Calendar'[Date]; _StartDate; _CurrentDate ) VAR _RunningTotal = CALCULATE( SUMX( System; 'System'[ProfitUSD] ); _Range ) RETURN _RunningTotalI hope this help, if so please mark as a solution. Kudos are welcome😀
- hcova71 year agoFrequent Visitor
Thank you very much for your perfect solution. You don't know how much time I have been able to reduce with this measure. Warm regards