Forum Discussion
Helper IIIQuater comparison based on custom Quarter Sequence No
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.
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 ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
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 ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
- SamWiseOwl
Super User
Nice work!
mbudimanHi Tom Shen, thank you so much for the solution. Yes, it works 😃
- 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
- 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-ifThen 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.
- 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.
