How to subtract the value from previous value through DAX

[Anonymous]

Hi team,

I have a data which looks somewhat like this:

SKU	Date	Fulfilment	Supply	Stock
18308	3/15/2020	111	336	884
39454	3/15/2020	195	473	363
35280	3/15/2020	160	120	773
61282	3/15/2020	117	384	231
39096	3/15/2020	121	350	463
43057	3/15/2020	121	367	733
62553	3/15/2020	132	492	572
56279	3/15/2020	144	155	122
28502	3/15/2020	190	204	340
27647	3/15/2020	143	496	944
36753	3/15/2020	172	446	353
73256	3/15/2020	112	184	737
12420	3/15/2020	125	200	494
30678	3/15/2020	112	207	919
6879	3/15/2020	191	179	833
18308	3/16/2020	182	381	854
39454	3/16/2020	175	119	225
35280	3/16/2020	178	300	409
61282	3/16/2020	136	312	204
39096	3/16/2020	193	199	956
43057	3/16/2020	191	351	297
62553	3/16/2020	178	140	957
56279	3/16/2020	188	306	749
28502	3/16/2020	170	105	518
27647	3/16/2020	103	101	835
36753	3/16/2020	139	268	462
73256	3/16/2020	122	215	571
12420	3/16/2020	119	272	656
30678	3/16/2020	197	326	887
6879	3/16/2020	193	302	240
18308	3/17/2020	123	311	574
39454	3/17/2020	170	491	918
35280	3/17/2020	100	291	558
61282	3/17/2020	110	161	714
39096	3/17/2020	153	433	820
43057	3/17/2020	132	266	363
62553	3/17/2020	195	277	480
56279	3/17/2020	191	136	774
28502	3/17/2020	194	436	987
27647	3/17/2020	151	247	485
36753	3/17/2020	101	338	234
73256	3/17/2020	116	114	950
12420	3/17/2020	171	403	600
30678	3/17/2020	127	140	944
6879	3/17/2020	160	170	913

What I am doing here is that I am showing this data on a line chart which has supply and stock lines and date is the axis.

What I want to achieve is, let's say if my graph is starting from 15/3/2020, it should show the supply line as it is, but the stock line should be (stock - supply + fulfilment). And the value that I get for 15th should be the base value for 16th march, that is, let's say my stock value for 15th comes out to be 450 (example), the base for stock value for 16th should be 450 and not the actual value that I have.

If you focus closely on the data, the stock value for a particular SKU is not changing throughout the data but fulfilment and supply change accordingly. Also, there  could be a possibility that the SKU is not presented for all of the dates. It could miss a few dates in between. In this case, stock should pick up the last calculated value for that particular SKU.

I am showing the line chart at an overall level and it would drill down to SKU level through slicers.

Can I achieve something that I am looking for?

Please help

Thanks in advance,

@Greg_Deckler @parry2k @amitchandak @Ashish_Mathur @Cmcmahan 

[Icey]

Hi @Anonymous ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards,
Icey

[Ashish_Mathur]

Hi,

Based on the data that you have share, show the expected result in a simple Table format.  Please modify your original data to account for missing dates as well.

[amitchandak]

@Anonymous 

Try

Cumm Sales = CALCULATE(SUM(Sales[stock ]) -SUM(Sales[supply])+ SUM(Sales[fulfilment]),filter(date,date[date] <=maxx(date,date[date])))

///////////OR 
Cumm Sales = 
var _min = Minx(Date,date[Date])
var _min = Maxx(Date,date[Date])
return
CALCULATE(SUM(Sales[stock ]) -SUM(Sales[supply])+ SUM(Sales[fulfilment]),filter(all(date),date[date] >=_min date[date]<=_max ))

[Anonymous]

Hi @amitchandak ,

Thanks for replying.

However, I tried both the methods but somehow I am still getting the same result (i.e. the measure is not working as it should)

PFB the measure I wrote:

VAR dem = SUMX( SUMMARIZE(Sheet1, Sheet1[Material], "_max", MAX(Sheet1[On-Hand Inventory])), [_max])

VAR ful = CALCULATE(SUM(Sheet1[Quantity]), Sheet1[Type1] = "Fulfilment")

VAR sup = CALCULATE(SUM(Sheet1[Quantity]), Sheet1[Type1] = "Supply")

var _min = Minx(Sheet1,Sheet1[Date])

var _max = Maxx(Sheet1,Sheet1[Date])

return

CALCULATE( dem - sup + ful ,filter(all(Sheet1),Sheet1[Date] >= _min && Sheet1[Date]<= _max ))

Am I doing something wrong here?

Thanks

[Icey · Accepted solution]

Hi @Anonymous ,

Please check if this is what you want:

1. Create a column like so:

HasStock =
VAR t =
    FILTER ( 'Table', [SKU] = EARLIER ( 'Table'[SKU] ) )
VAR Stock =
    CALCULATE (
        MAX ( 'Table'[Stock] ),
        FILTER ( t, [Date] = MINX ( 'Table', 'Table'[Date] ) )
    )
RETURN
    Stock
        - CALCULATE (
            SUM ( 'Table'[Supply] ),
            FILTER ( t, [Date] <= EARLIER ( 'Table'[Date] ) )
        )
        + CALCULATE (
            SUM ( 'Table'[Fulfilment] ),
            FILTER ( t, [Date] <= EARLIER ( 'Table'[Date] ) )
        )

2. Create a Line chart.

For more details, please check the attached PBIX file.

Best Regards,

Icey

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

[Greg_Deckler]

Yes, you need to use EARLIER. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...