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.
Hi there,
I have a slicer based on a table that I created called "Time Metric" that I inserted with values as shown in the image below. The idea here is that when someone picks eg Month to date, the measure returns Month to date total sales. Previous month shows previous month total sales etc.
Dynamic TI Measure =
IF(
HASONEVALUE(TimeMetric[Metric to display]),
SWITCH(
VALUES(TimeMetric[Metric to display]),
"Month to date", [Total MTD Sales],
"Previous Month", CALCULATE([xTotal Sales], PREVIOUSMONTH('Calendar'[Date])),
"Previous Year", calculate([xTotal Sales], PREVIOUSYEAR('Calendar'[Date])),
"Year to date",TOTALYTD([xTotal Sales],'Calendar'[Date])))
Clearly there is something wrong with my code because it doesn't work! Any suggestions?
Solved! Go to Solution.
Thank you to both @Whitewater100 & @tamerj1 for your suggestions!
I tried both and neither worked. Which got me thinking that maybe there was some other problem. So I changed the code to just return an existing measure that I know works and again, I got blanks! Now I knew something other than the code was at the root of the problem!
I then realised I had put a "non-date" dimension into my matrix (Category). Of course, without a date dimension, any time intelligence functions don't work! 🙊🤔
Dynamic TI Measure =
var selection = SELECTEDVALUE(TimeMetric[Metric to display])
Return
SWITCH (
Selection,
"Month to date", [Total MTD Sales],
"Previous Month", CALCULATE ( [xTotal Sales], PREVIOUSMONTH ( 'Calendar'[Date] ) ),
"Previous Year", CALCULATE ( [xTotal Sales], PREVIOUSYEAR ( 'Calendar'[Date] ) ),
"Year to date", TOTALYTD ( [xTotal Sales], 'Calendar'[Date] )
)
Hi:
I'm sorry that measure didn't work for you. It weird that it did not perform as expected. Probably some minor difference between the models or measures.
I tested it and it worked fine. I have all my slection measure pre-defined and maybe that's the difference. Anyways I'll post the image of it working. Thank you to too DataSkills for sticking with it.
Thank you to both @Whitewater100 & @tamerj1 for your suggestions!
I tried both and neither worked. Which got me thinking that maybe there was some other problem. So I changed the code to just return an existing measure that I know works and again, I got blanks! Now I knew something other than the code was at the root of the problem!
I then realised I had put a "non-date" dimension into my matrix (Category). Of course, without a date dimension, any time intelligence functions don't work! 🙊🤔
Dynamic TI Measure =
var selection = SELECTEDVALUE(TimeMetric[Metric to display])
Return
SWITCH (
Selection,
"Month to date", [Total MTD Sales],
"Previous Month", CALCULATE ( [xTotal Sales], PREVIOUSMONTH ( 'Calendar'[Date] ) ),
"Previous Year", CALCULATE ( [xTotal Sales], PREVIOUSYEAR ( 'Calendar'[Date] ) ),
"Year to date", TOTALYTD ( [xTotal Sales], 'Calendar'[Date] )
)
I forgot to add return..and had an extra )
New Result =
var selection = SELECTEDVALUE(TimeMetric[MetrictoDisplay])
return
SWITCH(selection,
"Month to Date", [Total MTD Sales],
"Previous Month", [Prev Month Measure],
etc
etc no comma after last one
)
Tried this but it didn't work. Gives a blank result.
Dynamic TI Measure =
var selection = SELECTEDVALUE(TimeMetric[Metric to display])
return
SWITCH(selection,
"Month to date", [Total MTD Sales],
"Previous Month", CALCULATE([xTotal Sales], PREVIOUSMONTH('Calendar'[Date])),
"Previous Year", calculate([xTotal Sales], PREVIOUSYEAR('Calendar'[Date])),
"Year to date",TOTALYTD([xTotal Sales],'Calendar'[Date]))
hi @DataSkills
You may try
Dynamic TI Measure =
SWITCH (
TRUE (),
SELECTEDVALUE ( TimeMetric[Metric to display] ) = "Month to date", [Total MTD Sales],
SELECTEDVALUE ( TimeMetric[Metric to display] ) = "Previous Month", CALCULATE ( [xTotal Sales], PREVIOUSMONTH ( 'Calendar'[Date] ) ),
SELECTEDVALUE ( TimeMetric[Metric to display] ) = "Previous Year", CALCULATE ( [xTotal Sales], PREVIOUSYEAR ( 'Calendar'[Date] ) ),
SELECTEDVALUE ( TimeMetric[Metric to display] ) = "Year to date", TOTALYTD ( [xTotal Sales], 'Calendar'[Date] )
)
HI:
You can try this:
Result =
var selection = SELECTEDVALUE(TimeMetric[MetrictoDisplay])
SWITCH(selection),
"Month to Date", [Total MTD Sales],
"Previous Month", [Prev Month Measure],
etc
etc no comma after last one
)
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |