Calculate Average for Last 3 periods and change from previous period using CUSTOM calendar

[mbudiman]

hello,

My company is using custom Financial calendar period. I need help to derive :

1. Average value for last 3 Quarter

2. Delta or Changes from previous Quarter to current Quarter by Inventory Category

The sample pbix can be downloaded here Inventory.pbix

Calendar table contains these columns :

- Fiscal Year, example : FY25, FY24 

- Fiscal Qtr, example : FY25Q1, FY25Q2, FY25Q3

- Qtr_Seq_No, where 0 means current Qtr, -1 means last 1 Qtr, -2 means last 2 Qtr

Inventory table contains these columns :

- Fiscal Qtr

- InventoryType

- Price Range

- Product

- Shipped Qty

Inventory Category table contains these columns:

- Inventory Category

- Inventory Type

- Price Range

Table Relationship

Calendar table is linked to Inventory table by Fiscal Qtr column

Inventory Category table in linked to Inventory table by Inventory Type and Price Range.

Challenge :

There is no link from Calendar table to Inventory Category table. However, we need to report Qtr changes by Inventory Category.

The following is the expected result from the data in pbix

Pls advise what is DAX statement to achieve "Change from previous Qtr" by Product and "Average Shipped Qty last 3 Qtr" by Inventory Category.

Thank you.

[Anonymous · Accepted solution]

Hi @mbudiman ,

Please try:

Change from previous Qtr = 
VAR __prevs_qtr = CALCULATE(SUM('Inventory'[Shipped Qty]),'Inventory calendar'[QTR_SEQ_NO]=-1,ALL('Inventory calendar'))
VAR __cur_qtr = SUM('Inventory'[Shipped Qty])
VAR __output= __cur_qtr - __prevs_qtr
RETURN
    __output

Average Shipped Qty from last 3 Qtr = 
VAR __last_3_qtr= CALCULATE(SUM('Inventory'[Shipped Qty]),'Inventory calendar'[QTR_SEQ_NO]>=-3,'Inventory calendar'[QTR_SEQ_NO]<=-1,ALL('Inventory calendar'))
VAR __output = DIVIDE(__last_3_qtr,3)
RETURN
    __output

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[mbudiman]

hi Gao,

The solution provided works. Thanks so much for your help.

[rajendraongole1]

Hi @mbudiman  - please find the attached pbix file. created two measure related to bring the expected result. please check and i hope it works. 

[mbudiman]

hi @ rajendraongolel

Thank you for sharing your solution. It does not work the way I want to present it (to show last qtr change in the same table that show current qtr value).