Quarterly Inventory DAX

[gardas_swathi]

Hello Everyone, I am trying to create a QuarterlyInventoryMeasure. I have inventory table with record for each date and product. For Each Quarter i have take value as of last day of previous quarter value and show as  QuarterlyInventory. It should not show value for other dates or weeks/months in the quarter. I need to implment this in Analysis Server Tabular Model. Appreciate your help

Below is the sample data and expected output

I have Inventory Table with below format

DateID InventoryUnits ProductID
12-31-2020 4000 1
1-1-2021 5000 1
1-2-2021 2500 1
1-3-2021 4000 1

1-31-2021 5000 1
2-01-2021 4500 1
2-26-2021 3200 1
2-28-2021 4000 1

3-30-2021 6000 1
3-31-2021 4000 1
4-01-2021 3000 1
4-28-2021 4300 1
5-01-2021 2500 1
5-31-2021 3000 1

Expected OutPut- Quarterly Inventory should take last day of previous quarter number as the current quarterly Inventory

DateID InventoryUnits QuarterlyInventory
12-31-2020 4100
1-1-2021 5000 4100
1-2-2021 2500
1-3-2021 4000

1-31-2021 5000
2-01-2021 4500
2-28-2021 4000

3-30-2021 6000
3-31-2021 4000
4-01-2021 3000 4000

[HashamNiaz · Accepted solution]

Hi @gardas_swathi !

You can use following DAX to create a measure;

PrevQtrInventory = 
    VAR _LastDate =  LASTDATE(PREVIOUSQUARTER('Calendar'[Date]))
RETURN
    CALCULATE( MAX('Inventory'[InverntoryUnit]), _LastDate) 

This measure will calculate PrevQtr LastDate from Calendar dimension and then get MAX(InventoryUnits) from Inventory table.

You can replace, Inventory with your table name & InventoryUnits with your column. Also you need to have a Calendar / Date dimension connected to your Inventory table, or you can use Inventory table.

You can place ProductID, filter in CALCULATE() filter part which i left for now.

Regards,

Hasham