Need help on classifying value

[VoltesDev]

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 · Accepted solution]

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.

[AlexisOlson]

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
    )

[smpa01]

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

[VoltesDev]

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.

[VoltesDev]

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

[amitchandak]

@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/1...

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

[VoltesDev]

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,