Cumulative substraction over categories

[Anonymous]

I have a matrix table in PBI and I would like to calculate a kind of cumulative substraction over the categories. The main goal is to achieve the "Expected W-1" field, so in a nutshell: for Category 1, the Expected W-1 calculation calculated in this way 10119 - 7427 = 2692, 

Further information that I am using a simple DAX measure = 
Count = Count(fact_table) (this is required because additonal filtering can be applied in the future. 

CATEGORIES	Current W-1	Expected W-1	How it is calculated?
Category 1	10119	2692	Category 1 - Category 2
Category 2	7427	3312	Category 2 - Category 3
Category 3	4115	2127	Category 3 - Category 4
Category 4	1988	109	Category 4 - Category 5
Category 5	1879	97	Category 5 - Category 6
Category 6	1782	383	Category 6 - Category 7
Category 7	1399	1399	Category 7
Total	28709	10119

Is there any way to solve this?

[ahmedashour]

Just create the following visual calculation

Expected W-1 = [Current W-1] - NEXT([Current W-1])

[tamerj1]

Hi @Anonymous 

what determines the order of CATEGORIES? What is the DAX code of the Current W1 measure?

[Anonymous]

The simplified model looks like this: 

In a nutshell I am ordering the "Category_label" field with the "Category_order" field with the built in function (under the "Column tools"), the "Category_order" field is integer. 

The Current W1 measure looks like this: 

Count.AllOpportunities = 
VAR previousweek = [Date.8to14Days]
RETURN
CALCULATE(COUNT('fact_table'[UniqueID]),FILTER('fact_table','fact_table'[Event_date]> previousweek))

 
The previousweek variable returns a single date to determine what is the cut off date. 

[tamerj1 · Accepted solution]

@Anonymous 

Please try

Count.AllOpportunities.New =
VAR CategoriesAndCounts =
ADDCOLUMNS (
ALLSELECTED ( Category_Table[Category_Order] ),
"@Count",
CALCULATE (
[Count.AllOpportunities],
ALLEXCEPT ( Category_Table, Category_Table[Category_Order] )
)
)
RETURN
SUMX (
VALUES ( Category_Table[Category_Order] ),
VAR CurrentOrder = Category_Table[Category_Order]
VAR CurrentCount = [Count.AllOpportunities]
VAR NextCount =
MAXX (
TOPN (
1,
FILTER ( CategoriesAndCounts, Category_Table[Category_Order] > CurrentOrder ),
Category_Table[Category_Order], ASC
),
[@Count]
)
RETURN
CurrentCount - NextCount
)

[Anonymous]

and it worked 😮 thank you so much! but I really need to ask that did you use any kind of help, because that was outstanding

[tamerj1]

@Anonymous 

Definitely no AI. Not even a laptop 😂 I just typed the code using my phone