Forum Discussion
Running total between two selected periods
Hello, I have a fact table with a relationship to a calendar table marked as Date table. Relation ship is one to n based on a date column. I can create time intelligence clculations like YTD, Previ...
Hello,
Thanks for all your suggestions. I finally found a way to calculate this running total using a formula like this one.
Cumulative Dollar := IF ( MIN ( 'Dim_Calendar'[ID_Date] ) <= CALCULATE ( MAX ( BOL[Sailing_Date_Key] ), ALL ( BOL ) ); CALCULATE(SUM(Bol[AmountUSD]), Filter( ALLSELECTED('Dim_Calendar'),'Dim_Calendar'[DateASDate]<=MAX('Dim_Calendar'[DateASDate]))) )Which produces exactly the result I want
YYYYWK AmountUSD Cumulative
201846 1'327'243 1'327'243 201847 1'192'890 2'520'134 201848 1'604'916 4'125'049 201849 745'256 4'870'306 201850 20'792 4'891'098 201851 4'891'098 201852 4'891'098 201901 4'891'098 201902 4'891'098 201903 4'891'098 201904 4'891'098 201905 4'891'098 201906 4'891'098 201907 32'834 4'923'932 201908 4'923'932 201909 4'923'932
My running total should not have Running Total with date on rows.
I should use Month Year. Format "May 2019".
As the solution use:
ALLSELECTED ( 'Sailing Date'[Sailing Date] )
ALL ( 'Sailing Date'[Sailing Date] ),
I change to
ALLSELECTED ( 'Sailing Date') Without the column.
ALL ( 'Sailing Date') Without the column.
So it doesn't matter which column from calender table Used on rows, the Running Total will Work.
v-jiascu-msft/Dale , Thank you for your support and also Anonymous for creating the Post.
Best Regards
Amdi
The code is:
Measure 7.2 =
VAR minDate =
CALCULATE (
MIN ( 'Date_2'[Date] );
ALLSELECTED ( Date_2)
)
RETURN
CALCULATE (
[Sales Amount];
FILTER (
ALL ('Date_2' );
'Date_2'[Date] >= minDate
&& 'Date_2'[Date] <= MAX ( 'Date_2'[Date] )
)
)