Forum Discussion

gardas_swathi's avatar
gardas_swathi
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago
Solved

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

 

 

 

  • HashamNiaz's avatar
    HashamNiaz
    5 years ago

    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

1 Reply

  • HashamNiaz's avatar
    HashamNiaz
    Icon for Solution Sage rankSolution Sage
    5 years ago

    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