Need Help with Cumulative difference based on item#

[Anonymous]

Hi,

I am reaching out to this group for help in calculating cumulative difference of quantity for an item. and the calculation for first instance of the item will be base calculation where as the other instances of the same item follow the base calc differnce from the total quantity. My example here would explain better than my words. any help on this is highly appreciated !! 

Thanks!

Description	Item #	Month Year	Total Fqty	Required ( same as Total Fqty)	Curr Released Inv Qty (Base qty)	Curr Quarantine Inv Qty (Base Qty)	Open Qty	Qty Remaining (**bleep** Diff)	Calc logic required
XXXX	Item0001	Apr-2020	1	1	15	0	0	14	Only for the first row for the item - the calculation is  Required - (Curr Released Inv Qty + Curr Qurantine Inv Qty + Open Qty)
XXXX	Item0001	May-2020	9	9	15	0	0	5	Above value + (Open Qty - Required)
XXXX	Item0001	Jun-2020	13	13	15	0	0	-8	Above value + (Open Qty - Required)
YYYY	Item0002	Apr-2020	2	2	239	0	0	237	follow the first value calculation for first instance of the item Only for the first row for the item - the calculation is  Required - (Curr Released Inv Qty + Curr Qurantine Inv Qty + Open Qty)
YYYY	Item0002	May-2020	18	18	239	0	0	219	Above value + (Open Qty - Required)
YYYY	Item0002	Jun-2020	26	26	239	0	0	193	Above value + (Open Qty - Required)

[v-alq-msft]

Hi, @Anonymous 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[Anonymous]

thanks for trying, but the solution mentioned didnt work for the second item, which has to reset the calculation. I have manged to write a measure calculation on my own. 

[v-alq-msft]

Hi, @Anonymous 

If possible, could you please share the measure to help the other members who have same problems find it more quickly. Thanks.

Best Regards

Allan

[v-alq-msft]

Hi, @Anonymous 

You may create a calculated column and a measure as below. The pbix is attached in the end.

FormattedMonthYear = VALUE(FORMAT(Sheet1[Month Year],"yyyymm"))
Qty Remaining = 
var _base =
CALCULATE(
    AVERAGE(Sheet1[Curr Released Inv Qty (Base qty)]),
    FILTER(
        ALL(Sheet1),
        Sheet1[Item #] = SELECTEDVALUE(Sheet1[Item #])
    )
)
var _required = 
CALCULATE(
    SUM(Sheet1[Required ( same as Total Fqty)]),
    FILTER(
        ALL(Sheet1),
        Sheet1[Item #] = SELECTEDVALUE(Sheet1[Item #])&&
        Sheet1[FormattedMonthYear]<=SELECTEDVALUE(Sheet1[FormattedMonthYear])
    )
)
return
_base-_required

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[negi007]

@AnonymousYour calculation seems to be little complicated to achieve but not impossible. I request you to share your excel file with the calculation, will then suggest appropriate solution to you. Thanks

[Anonymous]

Thanks for the reply, i am unable to attach my PBI file or Excel file in this post. How can i do that?

[Anonymous]

Attached the link to files here. 

PBIX file 

Inv calc comm 

[Anonymous]

any help on this calculation is much appreciated. thanks!

[negi007]

@AnonymousYou could use google drive or one drive to attached powerbi file. Else you can share your excel file with formula calculation that would also help

[amitchandak]

@Anonymous , not very clear what you mean by the above value. The logic does not fit in

We can create a measure like

Cumm balance= CALCULATE(SUM(Table[Released]),filter(date,date[date] <=max(Table[Date])))

- CALCULATE(SUM(Table[Required]),filter(date,date[date] <=max(Table[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

Thanks Amit for the reply, Above here refers to the previous value of the item's reamin Qty.

XXXX	Item0001	Apr-2020	1	1	15	0	0	14
XXXX	Item0001	May-2020	9	9	15	0	0	5  (14- (0+9)) (Previous value + (Open Qty - Required Qty))