cancel
Showing results for
Did you mean: Anonymous
Not applicable

## 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 "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
1 ACCEPTED SOLUTION  Solution Sage

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] ),
) - CALCULATE (
SUM ( Data[AmountInReportingCurrency] ),
Data[Level4TransactionTypeDescription] = "Inward Unearned Premium Reserve"
),
"Post-Switch"CALCULATE (
SUM ( Data[AmountInReportingCurrency] ),
),
"Switch"CALCULATE (
SUM ( Data[AmountInReportingCurrency] ),
ALL ( Data ),
Data[technicalyear] <= EARLIER ( Data[technicalyear] ),
) - CALCULATE (
SUM ( Data[AmountInReportingCurrency] ),
ALL ( Data ),
Data[technicalyear] <= EARLIER ( Data[technicalyear] ),
)
)
) Proud to be a Datanaut! 2 REPLIES 2  Solution Sage

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] ),
) - CALCULATE (
SUM ( Data[AmountInReportingCurrency] ),
Data[Level4TransactionTypeDescription] = "Inward Unearned Premium Reserve"
),
"Post-Switch"CALCULATE (
SUM ( Data[AmountInReportingCurrency] ),
),
"Switch"CALCULATE (
SUM ( Data[AmountInReportingCurrency] ),
ALL ( Data ),
Data[technicalyear] <= EARLIER ( Data[technicalyear] ),
) - CALCULATE (
SUM ( Data[AmountInReportingCurrency] ),
ALL ( Data ),
Data[technicalyear] <= EARLIER ( Data[technicalyear] ),
)
)
) Proud to be a Datanaut!  Anonymous
Not applicable

This is extremely helpful and did the trick. Thank you @LivioLanzo! Announcements #### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.  