Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Switch between measures?

I would like to re-create in Power BI a pivot table I've created in Excel. Sample Data - https://drive.google.com/open?id=1TyEioklmd_MUEzkq03NeGZ6TA0BW-_su The metric I am trying to re-create is the "...
  • LivioLanzo's avatar
    7 years ago

    Based on the results you are showing, the column "Switch Logic" should equal "Switch" for all the records of year 2017. The column "technicalperiodcode" should be of type number and not text

     

    Then the following DAX should get you the right results:

     

     

    Measure =
    SUMX (
        SUMMARIZE (
            Data,
            Data[technicalyear],
            Data[Switch Logic]
        ),
        SWITCH (
            [Switch Logic],
            "Pre-Switch"CALCULATE (
                SUM ( Data[AmountInReportingCurrency] ),
                Data[Level4TransactionTypeDescription] = "Inward Estimated Premium"
            ) - CALCULATE (
                    SUM ( Data[AmountInReportingCurrency] ),
                    Data[Level4TransactionTypeDescription] = "Inward Unearned Premium Reserve"
                ),
            "Post-Switch"CALCULATE (
                SUM ( Data[AmountInReportingCurrency] ),
                Data[Level4TransactionTypeDescription] = "Inward Booked Premium"
            ),
            "Switch"CALCULATE (
                SUM ( Data[AmountInReportingCurrency] ),
                ALL ( Data ),
                Data[technicalyear] <= EARLIER ( Data[technicalyear] ),
                Data[Level4TransactionTypeDescription] = "Inward Booked Premium"
            ) - CALCULATE (
                    SUM ( Data[AmountInReportingCurrency] ),
                    ALL ( Data ),
                    Data[technicalyear] <= EARLIER ( Data[technicalyear] ),
                    Data[Level4TransactionTypeDescription] = "Inward Estimated Premium"
                )
        )
    )