Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gardas_swathi
Employee
Employee

Quarterly Inventory DAX

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

 

 

 

1 ACCEPTED SOLUTION
HashamNiaz
Solution Sage
Solution Sage

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

View solution in original post

1 REPLY 1
HashamNiaz
Solution Sage
Solution Sage

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors