Hello,
I have been trying to resolve this for days. I`d be glad if you could help me to figure this out.
I`m have a running total measure which works fine. This measure shows the running total of contract amount, and I use it to show running total contract amount by accounting month and year. The issue is that each contract has an effective date and expiration date and I have to exclude contracts from the running total when they`re expired. For example, if I look at 7-2021, I shouldn't see amounts related to contracts expired at 7-2021.
I created an example Power BI and excel file to demonstrate the case but i can't attach it to my messeage for some reason, so I have to try to explain in this post.
I have two tables:
1) Journal Entires: It is the main fact table in my data model. I calculate annualized sales amount by using this table. I also use Accounting Date field from this table to see annualized sales amount by month and year.
2) Contract Details: It is a dimension table where we find contract expiration date. The table is connected to Journal Entries table with an independent join key. The relationship is one to many.
In our example, we have 3 contract for demonstration purposes. Jounal Entries table contains all amounts(entries) related to contracts.
Contract No | Annualized Sales Amount | Contract Expiration Date |
5489447 | 52,057 | 6/30/2020 0:00 |
2164578 | 5,759,814 | 6/30/2021 0:00 |
1356482 | 7,128,072 | 6/30/2022 0:00 |
I`m calculating the current running total by using this formula:
Annualized Sales Running Total = CALCULATE('Journal Entries'[Annualized Sales Amount], FILTER(ALLSELECTED('Journal Entries'),'Journal Entries'[Accounting Date].[Date] <= MAX('Journal Entries'[Accounting Date].[Date])))
And I tried to create the desired results by using this formula, but it`s not working.
Annualized Sales Running Total (Expired Exluded) = VAR _sub=CALCULATETABLE(VALUES(Contract Details),FILTER(ALL(Contract Details), Contract Details[Contract Expiration].[Date]<MAX(Journal Entries[Accounting Date].[Date])))
Return
CALCULATE('Journal Entries'[Annualized Sales Amount], FILTER(ALLSELECTED('Journal Entries'),'Journal Entries'[Accounting Date].[Date] <= MAX('Journal Entries'[Accounting Date].[Date]))) - CALCULATE([Annualized Sales Amount],FILTER(Journal Entries,Journal Entries'[Accounting Date].[Date] in _sub))
Accounting Date from Journal Entries Table | Accounting Date from Journal Entries Table | DERIVED FROM Journal Entries Table | CURRENT RESULT | DESIRED RESULT |
Year | Month | Annualized Sales Amount | Annualized Sales Running Total | Annualized Sales Running Total |
2020 | February | 0 | 0 | 52,057 |
2020 | March | | 0 | 52,057 |
2020 | April | 52,057 | 52,057 | 52,057 |
2020 | May | 0 | 52,057 | 52,057 |
2020 | June | 0 | 52,057 | 52,057 |
2020 | July | 5,622,185 | 5,674,242 | 5,622,185 |
2020 | August | | 5,674,242 | 5,622,185 |
2020 | September | | 5,674,242 | 5,622,185 |
2020 | October | 58,460 | 5,732,702 | 5,680,645 |
2020 | November | | 5,732,702 | 5,680,645 |
2020 | December | | 5,732,702 | 5,680,645 |
2021 | January | -12,384 | 5,720,318 | 5,668,261 |
2021 | February | | 5,720,318 | 5,668,261 |
2021 | March | | 5,720,318 | 5,668,261 |
2021 | April | 91,553 | 5,811,871 | 5,759,814 |
2021 | May | | 5,811,871 | 5,759,814 |
2021 | June | | 5,811,871 | 5,759,814 |
2021 | July | 6,666,725 | 12,478,596 | 6,666,725 |
2021 | August | | 12,478,596 | 6,666,725 |
2021 | September | 47,096 | 12,525,692 | 6,713,821 |
2021 | October | | 12,525,692 | 6,713,821 |
2021 | November | 414,251 | 12,939,943 | 7,128,072 |
2021 | December | | 12,939,943 | 7,128,072 |
This is a very simpified version of my model. The actual model is huge with many other tables and the fact table has close to 200 million rows. So ideally, the solution also consider performance.
I found that the thread below is similar to my use case but I couldn't make it work for my self. @v-kelly-msft
https://community.powerbi.com/t5/Desktop/Calculating-cumulative-excluding-certain-values-depending-o...
I hope I was able to explain and it make sense.
Thank you so much for any help in advance!
