Subtract two columns with that are related with date

[Anonymous]

Hi folks , I have one table with these columns .

Closed        |    Closed things            Cumulative Closed things

20/4/2018  |             2                    |      2

22/4/2018  |             2                    |      4

25/4/2018  |             5                    |      9

                                                           'Actual opened things'

                                                           *THIS IS THE COLUMN I WANT TO CALCULATE

                                                            Which I want to be a Cumulative column which                                                            subtracts with the 'cumulative Closed things' only if

                                                           it has values on that date.*

Opened       |    Opened things   | Cumulative Open things |       Actual opened things

15/4/2018  |      10                      |   0+10=10                       |  10-0=0

17/4/2018  |      6                        |   10+6=16                       |  16-0=16

21/4/2018  |      9                        |   16+9=25                       |  25-2=23 

26/4/2018  |      11                      |    25+11=36                    |  36-9=27

I want to see in each date how many things are opened , with that being said I want for each date Cumulative Open Things - Cumulative Closed Things (only if it has closed things on before that date )

Appreciate any help on Dax or something like that .

Thanks guys. Anything you don't understand please let me know .

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can create columns to get Cumulative Closed things and Cumulative Open things, then create measure to get Actual opened things.

Cumulative Closed things = CALCULATE(SUM(Table1[Closed things]),FILTER(ALLSELECTED(Table1),Table1[Closed]<=EARLIER(Table1[Closed])))

Cumulative Open things = CALCULATE(SUM(Table2[Opened things]),FILTER(ALLSELECTED(Table2),Table2[Opened]<=EARLIER(Table2[Opened])))

Actual opened things =

VAR d=SELECTEDVALUE(Table2[Cumulative Open things])

VAR v= MAXX(TOPN(1,FILTER(Table1,Table1[Closed]<MAX(Table2[Opened])),Table1[Closed],DESC),Table1[Cumulative Closed things])

RETURN d-v

Return the result showing picture below.

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EcAWrkVKNoFBhaLEqgDO7D8B9nlb4vs5dpqIU3rBTzp72Q?e=PTb6TB

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you so much @v-xicai.  It works perfectly .