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

[EdipGashi]

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:

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

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:

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

Thank You
Edip Xh. Gashi

[Anonymous · Accepted solution]

Hi @EdipGashi ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Update the formula of table 'Calendar' as below

Calendar = 
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Timestamp'[ServiceDate] ), TODAY () + 10 ),
    "Qtrs",
        IF ( MONTH ( [Date] ) = 1, YEAR ( [Date] ) - 1, YEAR ( [Date] ) )
            & SWITCH (
                TRUE (),
                MONTH ( [Date] ) IN { 2, 3, 4 }, " Q1",
                MONTH ( [Date] ) IN { 4, 5, 6 }, " Q2",
                MONTH ( [Date] ) IN { 7, 8, 9 }, " Q3",
                " Q4"
            )
)

2.  Update the formula of measure [Total Number Last 5 Quarters] as below 

Total Number Last 5 Quarters = 
VAR _maxdate =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
VAR _eqtr =
    CALCULATE (
        MAX ( 'Calendar'[Qtrs] ),
        FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] = _maxdate )
    )
VAR _mindate =
    CALCULATE (
        MIN ( 'Calendar'[Date] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            YEAR ( 'Calendar'[Date] )
                = VALUE ( LEFT ( _eqtr, 4 ) ) - 1
                && RIGHT ( 'Calendar'[Qtrs], 2 ) = RIGHT ( _eqtr, 2 )
        )
    )
RETURN
    SUMX (
        FILTER (
            'Calendar',
            'Calendar'[Date] >= _mindate
                && 'Calendar'[Date] <= _maxdate
        ),
        [*Total Number]
    )

Best Regards