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 li...
  • 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.

smpa01's avatar
smpa01
Community Champion
4 years ago

VoltesDev  can you please provide sample data and desired output in table format?

  • 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