Creating Variances & Labels based on Prior Rows

[Anonymous]

Hello - I have a table with Years & Quarter # in Year, and parameters set up to select both (attaching sample data). I'm looking to create 2 things:

1. A custom column that labels, based on parameter, whether Qtr&Yr combination is Current Qtr&Current Yr, Prior Qtr&Current Yr, or Current Qtr&Prior Yr, like in table (assuming this could be done with Index column, i.e. 1 earlier is "Prior Qtr, Current Yr", and 4 earlier is "Current Qtr, Prior Year"?)

2. Variance measures to calculate the Q/Q variance of prior Qtr&Yr, and Y/Y variance of last year's Qtr&Yr

Would really appreciate the help - thanks!

FiscalYr	FiscalQtrId	Index	Value	Current/Prior Qtr Year?
2020	3	1	88.52
2020	4	2	51.94			Current Qtr ID	2
2021	1	3	55.57			Current Year	2023
2021	2	4	65.24
2021	3	5	64.14
2021	4	6	82.38
2022	1	7	3.63
2022	2	8	29.61	Current Quarter, Prior Year
2022	3	9	1.48
2022	4	10	27.65
2023	1	11	53.09	Prior Quarter, Current Year
2023	2	12	38.28	Current Quarter, Current Year
2023	3	13	67.51
2023	4	14	86.49
2024	1	15	6.27
2024	2	16	66.65
2024	3	17	33.92
2024	4	18	44.64

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try below steps:

1. below is my test table

Table:

2. create measure with below dax formula

Current/Prior Qtr Year =
VAR _today =
    TODAY ()
VAR _year =
    YEAR ( _today )
VAR _qtr =
    QUARTER ( _today )
VAR _fiscayear =
    SELECTEDVALUE ( 'Table'[FiscalYr] )
VAR _fiscalqtr =
    SELECTEDVALUE ( 'Table'[FiscalQtrId] )
RETURN
    SWITCH (
        TRUE (),
        _fiscayear = _year
            && _fiscalqtr = _qtr, "Current qtr,Current year",
        _fiscayear = _year
            && _fiscalqtr = _qtr - 1, "Prior qtr,Current year",
        _fiscayear = _year - 1
            && _fiscalqtr = _qtr, "Current qtr,Prior year",
        _fiscayear = _year - 1
            && _fiscalqtr = _qtr - 1, "Prior qtr,Prior year"
    )

Q/Q =
VAR cur_id =
    SELECTEDVALUE ( 'Table'[Index] )
VAR cur_val =
    SELECTEDVALUE ( 'Table'[Value] )
VAR _val =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = cur_id - 1 )
    )
RETURN
    cur_val - _val

Prior Year Q/Q =
VAR _fy =
    SELECTEDVALUE ( 'Table'[FiscalYr] )
VAR _fqtr =
    SELECTEDVALUE ( 'Table'[FiscalQtrId] )
VAR _val =
    SELECTEDVALUE ( 'Table'[Value] )
VAR pre_val =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[FiscalYr] = _fy - 1
                && 'Table'[FiscalQtrId] = _fqtr
        )
    )
RETURN
    _val - pre_val

3. add a table visual with fields and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.