How To Change or Replace Date Context of Measure

[CEllinger]

This is what my data table looks like. 

Date	Qty On Hand	Qty Expired	Qty Sold	Qty Available
Mar 2021	1000	200	300	500
Apr 2021		300	200
May 2021		150	400

Qty On Hand is from an Inventory Table.

Qty On Hand = Calculate(SUM(Inventory), Date=Mar 2021)

Qty Expired and Qty Sold numerical values. Qty Available is calculated as:

QTY Available = Calculate([Qty On Hand]-([Qty Expired] + [Qty Sold]))

How do I remove the Date Context from the Qty on Hand measure so that I can use it in the QTY Available calculation for the future months?

[amitchandak]

@CEllinger , You have to try like

QTY Available = Calculate([Qty On Hand], all(Table[Date])) -calculate([Qty Expired] + [Qty Sold]))

I think better would be like this with date table

Qty On Hand = Calculate([Qty On Hand], all(Date[Date])) + CALCULATE(SUM(Table[Qty Sold]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Qty Expired]),filter(date,date[date] <=maxx(date,date[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 :radacad sqlbi My Video Series Appreciate your Kudos.