Switch between measures?

Question

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 Answer

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&nbsp;Then the following DAX should get you the right results:&nbsp;&nbsp;Measure&nbsp;=SUMX&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;SUMMARIZE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Data,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Data[technicalyear],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Data[Switch&nbsp;Logic]&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;SWITCH&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Switch&nbsp;Logic],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"Pre-Switch",&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;Data[AmountInReportingCurrency]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Data[Level4TransactionTypeDescription]&nbsp;=&nbsp;"Inward&nbsp;Estimated&nbsp;Premium"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;-&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;Data[AmountInReportingCurrency]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Data[Level4TransactionTypeDescription]&nbsp;=&nbsp;"Inward&nbsp;Unearned&nbsp;Premium&nbsp;Reserve"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"Post-Switch",&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;Data[AmountInReportingCurrency]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Data[Level4TransactionTypeDescription]&nbsp;=&nbsp;"Inward&nbsp;Booked&nbsp;Premium"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"Switch",&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;Data[AmountInReportingCurrency]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALL&nbsp;(&nbsp;Data&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Data[technicalyear]&nbsp;&lt;=&nbsp;EARLIER&nbsp;(&nbsp;Data[technicalyear]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Data[Level4TransactionTypeDescription]&nbsp;=&nbsp;"Inward&nbsp;Booked&nbsp;Premium"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;-&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;Data[AmountInReportingCurrency]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALL&nbsp;(&nbsp;Data&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Data[technicalyear]&nbsp;&lt;=&nbsp;EARLIER&nbsp;(&nbsp;Data[technicalyear]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Data[Level4TransactionTypeDescription]&nbsp;=&nbsp;"Inward&nbsp;Estimated&nbsp;Premium"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;))&nbsp;

Forum Discussion

Switch between measures?

2 Replies

Related content

Switch measure

Alternative to SWITCH ()?

Switch Measure - conditional formatting

Switch -Containsstring

Order switching mid workflow