Switch between measures?

[Anonymous]

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 "Earned" metric, which is highlighted in the attached workbook. Here is how it works. The switch period is 201706. Prior to the switch period, the formula for the value is Estimated Premium minus Unearned Premium Reserve. In the switch period, the formula for the value is the difference between the cumulative booked premium and the cumulative estimated premium. After the switch period, the formula for the value is equal to the Booked Premium. I figured I could create 3 measures and then switch between them, but I am not having very much luck. Is anyone able to assist? Kind regards, Sergio

[LivioLanzo · Accepted solution]

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"
            )
    )
)

[Anonymous]

This is extremely helpful and did the trick. Thank you @LivioLanzo!