Quarterly calculation based on the parameter that decides when the quarter starts

Power BI
1

Dear all,

I have a request to calculate the 5 quarters for services, the current quarter and the previous 4. The current quarter can be changed because Q1 can start in the month of February (February, March, April), or in March (March, April, May) etc.
I made a calculation which count the “Total Numbers” within 15 months, based on the maximum date and if Quarter start in January works well:

Total Number Last 5 Quarters =
VAR ReferenceDate = MAX(‘Calendar’[Date])
VAR PreviousDate =
DATESINPERIOD(
‘Previous Calendar Date’[Date],
ReferenceDate,
-15,
MONTH
)
VAR Result =
CALCULATE([*Total Number],
REMOVEFILTERS(‘Calendar’),
KEEPFILTERS(PreviousDate),
USERELATIONSHIP( ‘Calendar’[Date],‘Previous Calendar Date’[Date])
)
RETURN
Result

this is the result:
image

I want,when I change the parameter, if the first quarter Q1 starts in the month Q-FEB and I select month Feb, Mar & Apr, then the calculation should start from the month of February, March and April for Q1.

This is the parameter:
Parameter = {
(“Q - JAN”, NAMEOF(‘Calendar’[Qtrs]), 0),
(“Q - OCT”, NAMEOF(‘Calendar’[Qtrs - 10]), 1),
(“Q - DEC”, NAMEOF(‘Calendar’[Qtrs - 12]), 2),
(“Q - FEB”, NAMEOF(‘Calendar’[Qtrs - 2]), 3),
(“Q - MAR”, NAMEOF(‘Calendar’[Qtrs - 3]), 4),
(“Q - APR”, NAMEOF(‘Calendar’[Qtrs - 4]), 5),
(“Q - NOV”, NAMEOF(‘Calendar’[Qtrs - 11]), 6),
(“Q - MAY”, NAMEOF(‘Calendar’[Qtrs - 5]), 7),
(“Q - JUN”, NAMEOF(‘Calendar’[Qtrs - 6]), 8),
(“Q - JUL”, NAMEOF(‘Calendar’[Qtrs - 7]), 9),
(“Q - AUG”, NAMEOF(‘Calendar’[Qtrs - 8]), 10),
(“Q - SEP”, NAMEOF(‘Calendar’[Qtrs - 9]), 11)
}

This is the calendar:
Calendar = ADDCOLUMNS (CALENDAR (MIN(Timestamp[ServiceDate]), TODAY()+10),
“Qtrs”, “Q”&FORMAT([Date], "Q ")&YEAR([Date]),
“Qtrs - 2”, “Q”&FORMAT(eomonth([Date],-1), "Q ")&YEAR(eomonth([Date],-1)),
“Qtrs - 3”, “Q”&FORMAT(eomonth([Date],-2), "Q ")&YEAR(eomonth([Date],-2)),
“Qtrs - 4”, “Q”&FORMAT(eomonth([Date],-3), "Q ")&YEAR(eomonth([Date],-3)),
“Qtrs - 5”, “Q”&FORMAT(eomonth([Date],-4), "Q ")&YEAR(eomonth([Date],-4)),
“Qtrs - 6”, “Q”&FORMAT(eomonth([Date],-5), "Q ")&YEAR(eomonth([Date],-5)),
“Qtrs - 7”, “Q”&FORMAT(eomonth([Date],-6), "Q ")&YEAR(eomonth([Date],-6)),
“Qtrs - 8”, “Q”&FORMAT(eomonth([Date],-7), "Q ")&YEAR(eomonth([Date],-7)),
“Qtrs - 9”, “Q”&FORMAT(eomonth([Date],-8), "Q ")&YEAR(eomonth([Date],-8)),
“Qtrs - 10”, “Q”&FORMAT(eomonth([Date],-9), "Q ")&YEAR(eomonth([Date],-9)),
“Qtrs - 11”, “Q”&FORMAT(eomonth([Date],-10), "Q ")&YEAR(eomonth([Date],-10)),
“Qtrs - 12”, “Q”&FORMAT(eomonth([Date],-11), "Q ")&YEAR(eomonth([Date],-11)),
“YearMonthShort”, FORMAT ( [Date], “mmm YYYY” ))

To decide when the quarter starts and from which month to start, I use these filters from the Filter Pane

image
image328×661 12.4 KB

At the moment, this calculation is showing me, but it is wrong because the Q2 that is being displayed should not be there, since the calculation should be from Q1, plus previous 4 quarters:
image

If anyone can help me, I would be very grateful.

Thank You
Edip Xh. Gashi

2

Just to understand a little better, you are looking to show 5 quarters (3 month periods), the Quarter number is not linked to a financial period, instead Q1 is selected from a parameter and from that selected value the previous 4 intervals of 3 months are shown as well?

Would just having one selection work, being the start month of Q1 the Quarter you are defining as the current?

I am not sure what the requirement for the Q-FEB is?

Hopefully someone has experienced this and can help more, a overview of the scenario may help.

3

Hi Kylie,

The requirement is that if Q-FEB is selected on the Parameter, it means that Quarter 1 starts from February, March and April.
If Q-MAR is selected, it means that Q1 starts from March, April, May

The current calculation is all right if the quarter starts in January, but we need to change the beginning of the quarter.

As for the months, 3 must be selected because there are other calculations in the report that must be displayed.

4

if that is the case and Q1 is not a financial period and just relates to the start, which is selected by the table named parameter, I dont think you can have relationships in this calculation, as it isnt fixed the months that belong to Q1 will change depending on the selection.

You would need to generate a calculated table that always starts at Q1 but the year changed depending on the selection and then spanned the previous 4 periods so that could be used in the visual columns, and then change the measure so it could work out a date range for each selected value to use as a filter for the total measure.

It would be easier if you used the actual quarters and used the parameter selection to create a measure that returns 1 or 0 if the qtr is in the selection and used that in the visual filter.

1 Like
5

Can someone tell me step by step how to do it?

6

Unless someone else has any suggestions, one that I thought of was that you could potentially create a table for each Q-Month Combo so it contained all the dates, Qtrs, etc grouped by Q-Jan, Q-Feb etc and load it as another table, create a many to many bi directional relationship between that and the date table and use the Q-Month column in the columns of the visual, your measure would need to be updated to return blank if the date wasnt in the selected quarter range which would be harvested from slicer like you would any other disconnected table.

Hopefully this may help others with their suggestions, or lead to conversations about the topic :slight_smile:

7

Bumping this post for more visibility from our experts and users.

8

Hi @edipgashi - can also think of this option only. Try this out. If something else comes to me I will let you know.

Thanks
Ankit J

1 Like
9

Hi @edipgashi

We noticed that your inquiry was left unsolved for quite some time now.
Looks like your inquiry was out of the experts’ and users’ bounds.

If you were sure you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, your membership also comes with relevant resources that may help you with your Power BI education, so we advise that you check these resources as well.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!