Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Solved! Go to 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"
)
)
)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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"
)
)
)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |