Quater comparison based on custom Quarter Sequence No

[mbudiman]

My company adopt manufacturing 4-4-5 calendar. So I created custom Fiscal_Qtr table and aggregated Sales_Qtr table. 

Fiscal_Qtr table contains : Fiscal_Yr, Fiscal_Qtr, Qtr_Sequence_No (where 0 means current Qtr, -1 means previous one Qtr,  -2 means previous two Qtr, +1 means next Qtr).

Sales_Qtr table contains : Fiscal_Qtr, Revenue, Sales_Qty

The two tables are joined based Fiscal_Qtr field (one to many).

Sample value of Fiscal_Qtr table:

Fiscal Yr        Fiscal_Qtr      Qtr_Sequence_No

FY24             FY24Q3         -2

FY24             FY24Q4         -1  (previous Qtr)

FY25             FY25Q1          0  (current Qtr)

I want to show Sales trend for 4 Qtr (based on Qtr_Sequence_No for 0, -1, -2, -3). How to create a custom measure of "Last_Qtr_Revenue" based on Qtr_Sequence_No ?

Appreciate if you could guide me. Thank you.

[v-xingshen-msft · Accepted solution]

Hi All,
Firstly  Kedar_Pande  and  SamWiseOwl thank you for yours solutions!
And @mbudiman , Since you are operating from two tables and it is a one-to-many relationship, we can try to use the related function to get the Sequence No to find the Revenue of the previous month, I hope my thoughts can help you!

Last_Qtr_Revenue = 
CALCULATE(
    SUM(Sales_Qtr_Table[Revenue]),
    FILTER(
        ALL(Sales_Qtr_Table),
        RELATED(Fiscal_Qtr_Table[Qtr_Sequence_No]) = MAX(Fiscal_Qtr_Table[Qtr_Sequence_No]) - 1
    )
)

You can check my pbix file if you have further questions, looking forward to your reply!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[mbudiman]

Hi Tom Shen, may I ask you another question related to this topic. The solution for 'Last Qtr Revenue' works, however, when I apply it to drill down by "Sales Region" for the same quarter, it does not automatically calculate Last Qtr Revenue by Sales Region. Appreciate your further advise. 

I've prepared sample pbix to show you, but not able to upload

[mbudiman]

Hi Tom Shen, thank you so much for the solution. Yes, it works 😃

[SamWiseOwl]

Nice work!

[Kedar_Pande]

Last_Qtr_Revenue = 
CALCULATE(
SUM(Sales_Qtr[Revenue]),
FILTER(
Fiscal_Qtr,
Fiscal_Qtr[Qtr_Sequence_No] = -1
)
)

If you want to create a custom measure that shows the trend for the last four quarters

Last_4_Qtrs_Revenue = 
CALCULATE(
SUM(Sales_Qtr[Revenue]),
FILTER(
Fiscal_Qtr,
Fiscal_Qtr[Qtr_Sequence_No] >= -3 && Fiscal_Qtr[Qtr_Sequence_No] <= 0
)
)

If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[mbudiman]

hi Kedar,

thank you for your advise. The solution does not achieve the outcome. I want the report to show changes from quarter to quarter, as below :

Fiscal Qtr    (Qtr_Sequence_No)     Revenue     (Last_Qtr_Rev)   Changes

FY2402        -3                                $200k         $250k                 -$50k

FY2403        -2                                $190k         $200k                 -$10k

FY2404        -1                                $220k         $190k                  $30k

FY2501        0                                  $275k         $220k                  $55k

Appreciate your further advise. Thanks in advance.

[SamWiseOwl]

Hi @mbudiman 

If I've understood you correctly.

Last Qtr Revenue =

var currYear = SelectedValue(table[Fiscal Yr ])

Return

Calculate(

               [Your calculation]

               ,All(calendartable), calendartable[Fiscal Yr] =currYear, calendartable[Qtr_Sequence_No] = -1)

Or to get real fancy create a Numeric Parameter:
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-what-if

Then replace the -1 with the measure the Numeric Parameter creates.

Then your users can choose between 0 and -2 with a slider.

[mbudiman]

hi SamWiseOwl,

Thank you for your advise. The report is to show current Qtr (Qtr_sequence_no = 0). There is no valud for 'SelectedValue', so your solution does not work. I want the report to show changes from quarter to quarter, as below :

Fiscal Qtr    (Qtr_Sequence_No)     Revenue     (Last_Qtr_Rev)   Changes

FY2402        -3                                $200k         $250k                 -$50k

FY2403        -2                                $190k         $200k                 -$10k

FY2404        -1                                $220k         $190k                  $30k

FY2501        0                                  $275k         $220k                  $55k

Appreciate your further advise. Thanks in advance.