Forum Discussion

VoltesDev's avatar
VoltesDev
Helper V
4 years ago
Solved

Need help on classifying value

Hello guys,

 

Need you help on my issue here, that if I have this kind of data :

What I need is to calculate the age of the ending qty. 

 

I have a setup of grouping to classify the age like this :

 

 

How to make a matrix that grouping the end quantity ?

The problem I noticed is I can't just use the Date and using Datediff, if lets say today is April 2021. because it will need to track when it is minus, it should substract the oldest qty first. This is actually for inventory movement transactions which usually we called it First In First Out (FIFO)

 

This is what I'm talking about:

If just by Sum with its respective date (with Datediff to April 2021), I will get like this :

 

But that is wrong, the correct result should be like this :

 

Both ending (available) qty is same, 12, but we cannot just sum the month of March to be -3, since the minus is supposed to substrat the previous qty which maybe not in the same month.

 

I wonder this kind of analysis / calculation can be achieve by DAX ? FYI, the data will also have different items (ItemsA, itemsB, etc) I just want to describe more clear so only use 1 item.

 

Please advice.

Thanks

 

 

 

 

 

 

 

 

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi， VoltesDev ；

    Try it.

    Measure = 
var _1m=CALCULATE(SUM([Qty]),FILTER(ALL('Table'),[Item]=MAX('Table'[Item])&&[month]="1 month"))
var _2m=CALCULATE(SUM([Qty]),FILTER(ALL('Table'),[Item]=MAX('Table'[Item])&&[month]="2 month"))
var _3m=CALCULATE(SUM([Qty]),FILTER(ALL('Table'),[Item]=MAX('Table'[Item])&&[month]=">2 month"))
var   _total=SWITCH( MAX([month]),"1 month",IF(_1m+_2m> 0&&_1m<0,_1m+_2m,0),"2 month",IF(_1m+_2m<0,_1m+_2m+_3m,_2m),">2 month",IF(_1m<0&&_1m+_2m>0,_3m-_2m,0))
return IF(HASONEVALUE('Table'[month]),_total,SUM('Table'[Qty]))

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    VoltesDev , If this based on today's date you can add a new column

     

    New Column =

    var _diff = datediff([Date], today(), Month())

    return

    Switch( True() ,

    _diff <= 1 , " 1 Month",

    _diff <= 2 , " 2 Month",

    "> 2 Month"

    )

     

    Use this in visual

     

     

    In case you need based on the selected date, You need a measure and then you need to use segmentation

    Dynamic Segmentation Bucketing Binning
    https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626


    Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

    • VoltesDev's avatar
      VoltesDev
      Helper V
      4 years ago

      Hi amitchandak ,

       

      I'm sorry, but I believe that only will not work. If I only use the date and calculate DateDiff, it will make the 1st month column -3 like my example. So the expectation is the 2nd matrix like this :

       

      Thanks,

    • VoltesDev's avatar
      VoltesDev
      Helper V
      4 years ago

      Hi smpa01 ,

       

      I'm sorry, I hope this is applicable ?

      ItemDateQty

      ItemA

      		9/1/202110
      ItemA10/1/20215
      ItemA11/1/20212
      ItemA11/1/2021-5
      ItemB10/1/20213
      ItemC9/1/20214
      ItemC10/1/20213
      ItemC11/1/2021-5

       

      And the grouping :

      AgeFromTo
      1 month030
      2 month3160
      > 2 month619999

       

      And the expectation is like this :

       

      Many thanks in advance.

      • VoltesDev's avatar
        VoltesDev
        Helper V
        4 years ago

        Hi smpa01 ,

         

        So sorry but I seems cannot get the table right, tried many ways, from copying from excel, insert table from this tools and copy from excel again until I tried to insert table again and key in manually one by one, but it looks terrible after post.

         

        But if I tried to copy back, it is already in table format. I'm not sure what's going on with this editor.

         

        Thanks

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    I haven't tested this logic carefully, but I think something like this might get you on the right track:

    2 month =
VAR MaxDate   = CALCULATE ( MAX ( Inventory[Date] ), ALL ( Inventory ) )
VAR StartDate = MaxDate - 60 //Days To
VAR EndDate   = MaxDate - 30 //Days From
VAR PriorQty =
    CALCULATE ( SUM ( Inventory[Qty] ), Inventory[Date] <= StartDate )
VAR AddedQty =
    CALCULATE (
        SUM ( Inventory[Qty] ),
        Inventory[Date] > StartDate,
        Inventory[Date] <= EndDate,
        Inventory[Qty] > 0
    )
VAR SubtractedQty =
    - CALCULATE (
        SUM ( Inventory[Qty] ),
        Inventory[Date] > StartDate,
        Inventory[Qty] < 0
    )
RETURN
    IF (
        PriorQty > SubtractedQty,
        AddedQty,
        PriorQty + AddedQty - SubtractedQty
    )
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi， VoltesDev ；

    Try it.

    Measure = 
var _1m=CALCULATE(SUM([Qty]),FILTER(ALL('Table'),[Item]=MAX('Table'[Item])&&[month]="1 month"))
var _2m=CALCULATE(SUM([Qty]),FILTER(ALL('Table'),[Item]=MAX('Table'[Item])&&[month]="2 month"))
var _3m=CALCULATE(SUM([Qty]),FILTER(ALL('Table'),[Item]=MAX('Table'[Item])&&[month]=">2 month"))
var   _total=SWITCH( MAX([month]),"1 month",IF(_1m+_2m> 0&&_1m<0,_1m+_2m,0),"2 month",IF(_1m+_2m<0,_1m+_2m+_3m,_2m),">2 month",IF(_1m<0&&_1m+_2m>0,_3m-_2m,0))
return IF(HASONEVALUE('Table'[month]),_total,SUM('Table'[Qty]))

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.