topic Re: Need help on classifying value in DAX Commands and Tips

Need help on classifying value

VoltesDev — Fri, 17 Dec 2021 10:19:32 GMT

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

Re: Need help on classifying value

amitchandak — Fri, 17 Dec 2021 13:48:56 GMT

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

Re: Need help on classifying value

VoltesDev — Fri, 17 Dec 2021 14:53:27 GMT

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,

Re: Need help on classifying value

smpa01 — Fri, 17 Dec 2021 14:56:37 GMT

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

Re: Need help on classifying value

VoltesDev — Fri, 17 Dec 2021 15:26:36 GMT

Hi smpa01 ,

I'm sorry, I hope this is applicable ?

Item	Date	Qty
ItemA	9/1/2021	10
ItemA	10/1/2021	5
ItemA	11/1/2021	2
ItemA	11/1/2021	-5
ItemB	10/1/2021	3
ItemC	9/1/2021	4
ItemC	10/1/2021	3
ItemC	11/1/2021	-5

And the grouping :

Age	From	To
1 month	0	30
2 month	31	60
> 2 month	61	9999

And the expectation is like this :

Many thanks in advance.

Re: Need help on classifying value

VoltesDev — Fri, 17 Dec 2021 15:32:40 GMT

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

Re: Need help on classifying value

AlexisOlson — Fri, 17 Dec 2021 22:32:30 GMT

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 )

Re: Need help on classifying value

v-yalanwu-msft — Tue, 21 Dec 2021 02:28:16 GMT

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.