Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am trying to create a new calculated column that shows staff over a time period. This new calculated column will also dyanmically represent the month and year, so that this changes when the report is refreshed. I am trying to add in the "Change" column as the difference between "Actual" and "Prior", but am getting the error:
Function 'SWITCH' does not support comparing values of type True/False with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.
My DAX expression is:
Dynamic Month Forecast =
var current_month = MONTH(Publish[Max Date])
var Actual = AND(Staff[Date] = Publish[Max Date], CONTAINSSTRING(Staff[Projection], "Actual Staff"))
var ActualName = FORMAT(Publish[Max Date], "mmm yyyy")
var Prior = AND(MONTH(Staff[Date]) = current_month - 1, CONTAINSSTRING(Staff[Projection], "Actual Staff"))
var PriorName = FORMAT(Publish[Max Date] - 1, "mmm yyyy")
var Change = Actual - Prior
var ChangeName = "Change from Prior Month"
var Forecast1 = AND(MONTH(Staff[Date]) = current_month + 1, CONTAINSSTRING(Staff[Projection], "Forecasted"))
var F1Name = FORMAT(Publish[Max Date] + 31, "mmm yyyy")
var result =
SWITCH(TRUE(),
Actual, ActualName,
Prior, PriorName,
Change, ChangeName,
Forecast1, F1Name,
)
return
result
I am using the switch at the end to dynamically represent the month.
I am hoping to get something that looks like this:
Can this be done? Is there something I need to add to the calculation?
Thanks for the help.
@DerekS , My advice would to create measure for this month and last month and have difference.
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
In calculation group you can control display of few member if needed