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
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 |
I had the same problem, it worked perfectly, thank you.