Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to subtract the value from previous value through DAX

Hi team,

 

I have a data which looks somewhat like this:

 

SKUDateFulfilmentSupplyStock
183083/15/2020111336884
394543/15/2020195473363
352803/15/2020160120773
612823/15/2020117384231
390963/15/2020121350463
430573/15/2020121367733
625533/15/2020132492572
562793/15/2020144155122
285023/15/2020190204340
276473/15/2020143496944
367533/15/2020172446353
732563/15/2020112184737
124203/15/2020125200494
306783/15/2020112207919
68793/15/2020191179833
183083/16/2020182381854
394543/16/2020175119225
352803/16/2020178300409
612823/16/2020136312204
390963/16/2020193199956
430573/16/2020191351297
625533/16/2020178140957
562793/16/2020188306749
285023/16/2020170105518
276473/16/2020103101835
367533/16/2020139268462
732563/16/2020122215571
124203/16/2020119272656
306783/16/2020197326887
68793/16/2020193302240
183083/17/2020123311574
394543/17/2020170491918
352803/17/2020100291558
612823/17/2020110161714
390963/17/2020153433820
430573/17/2020132266363
625533/17/2020195277480
562793/17/2020191136774
285023/17/2020194436987
276473/17/2020151247485
367533/17/2020101338234
732563/17/2020116114950
124203/17/2020171403600
306783/17/2020127140944
68793/17/2020160170913

 

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 

 

Need Help
Show and Tell
Tips and Tricks
  • Icey's avatar
    Icey
    6 years ago

    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.

     

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Icey
        Icon for Community Support rankCommunity Support
        6 years ago

        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.

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    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.

     

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    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