Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |