RangOver Sum and color formatting

[Applicable88]

Hello,

i haven't found the equivalent function to qlik's rangesum(). With a typical substraction I cannot return my wanted value.

I have following table:

Material	TotalOnStock	Reserve	Date	RangeSum
ZZZ	1200	200	01.07.2021	1000
ZZZ	1200	100	02.07.2021	900
ZZZ	1200	50	03.07.2021	850
ZZZ	1200	20	04.07.2021	830

I want a measure to calculate me the substraction from the "TotalOnStock" which is always the same value on each row, with the summed up Reserve from the same or if available the sum from all dates before like the numbers in "RangeSum" column. 

Other than that I also need a measure for color formatting, when in RangeSum it becomes zero or less its red and when positiv number its green. For example like this with different numbers:

Material	TotalOnStock	Reserve	Date	RangeSum
ZZZ	1200	500	01.07.2021	700
ZZZ	1200	400	02.07.2021	300
ZZZ	1200	300	03.07.2021	0
ZZZ	1200	50	04.07.2021	-50

Thank you very much in advance. 

Best. 

[amitchandak · Accepted solution]

@Applicable88 , A new measure , Assume TotalOnStock  and Reserve are measures

RangeSum= [TotalOnStock] -calculate(sum([Reserve]), filter(allselected(Table), Table[Date] <= max(Table[Date])))

[v-xulin-mstf · Accepted solution]

Hi @Applicable88,

Try measure as:

Rangesum = 
CALCULATE(
    MAX('Table'[TotalOnStock])-SUM('Table'[Reserve]),
    FILTER(
        all('Table'),
        'Table'[Date]<=MAX('Table'[Date])
    )
)

And then set conditon formatting as:

Here is the output:

The pbix is attached.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[amitchandak · Accepted solution]

@Applicable88 , A new measure , Assume TotalOnStock  and Reserve are measures

RangeSum= [TotalOnStock] -calculate(sum([Reserve]), filter(allselected(Table), Table[Date] <= max(Table[Date])))