none
DAX ParallelPeriod with dynamic interval set RRS feed

  • Question

  • Hi,

    i have the following DAX code to find the previous year premium in a table:

    CALCULATE (
                SUM ( 'Opl Quarterly As Of Amounts'[Gross Prem Inf] ),
                PARALLELPERIOD ( 'OPL Date'[Date], -1, YEAR )
            )

    The table has year end values (Quarter 4) for all history and will have the latest quarter for the current year (i.e. it will have quarter 3 for 2018).

    however, i want to make the -1 interval be dynamic based on the value in the quarter (1,2,3,4).  So if it's Quarter 1 or 2, i want to go back 2 years.  If it's 3 or 4, i want to go back 1 year.

    I created the following:

    CALCULATE (
                SUM ( 'Opl Quarterly As Of Amounts'[Gross Prem Inf] ),
                PARALLELPERIOD ( 'OPL Date'[Date], If('OPL Date'[Qrtr Num] = 1 || 'OPL Date'[Qrtr Num] = 2 ,-2, -1), YEAR )
            )

    but get the following error:

    "A single value for column Qtr Num in OPL Date cannot be determined.  this can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result"

    I can't seem to find the correct syntax to get it to work correctly.

    Tuesday, November 13, 2018 8:21 PM

All replies

  • Hi ScottCabral,

    Have a try this DAX expression:

    CALCULATE (
                SUM ( 'Opl Quarterly As Of Amounts'[Gross Prem Inf] ),
                PARALLELPERIOD ( 'OPL Date'[Date], If(SELECTEDVALUE('OPL Date'[Qrtr Num] )= 1 || SELECTEDVALUE('OPL Date'[Qrtr Num])= 2 ,-2, -1), YEAR )
            )

    Regards,

    Pirlo Zhang 


    MSDN Community Support<br/> Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    • Proposed as answer by Pirlo Zhang Wednesday, November 21, 2018 9:21 AM
    Wednesday, November 14, 2018 7:09 AM