Forum Discussion
Date Selection with Slicer
Hello,
I have a data set with date such as Jun 1 - Yesterday, I would like to have a slicer with two option, Yesterday / MTD.
How could I do that?
Thanks!
Anonymous,
Do you want to use a slicer to choose which measure(Yesterday or MTD) to display? If so, create a new table containing the measure name like below.
Then create the following similar measures in your original table, please note that there is no relationship between the new table and your original table.
salesMTD = TOTALMTD(SUM(Table6[Amount]),Table6[Date])
salesYesterday = CALCULATE(SUM(Table6[Amount]),FILTER(Table6,Table6[Date]=TODAY()-1))
selected measure1 = IF(HASONEVALUE(Table7[Measure]),SWITCH(FIRSTNONBLANK(Table7[Measure],Table7[Measure]),"Yesterday",[salesYesterday],"MTD",[salesMTD]),blank())
Reference:http://breaking-bi.blogspot.sg/2016/06/power-bipowerpivot-using-slicers-to.html
Regards,
Lydia Zhang
2 Replies
quick and dirty use calculated column with SWITCH or IF
not that beautiful though but if the data refreshes regularly and the refresh performance isnt an issue thats fine
if you dont want that build good time intelligence for your model theres tutorials
Anonymous,
Do you want to use a slicer to choose which measure(Yesterday or MTD) to display? If so, create a new table containing the measure name like below.
Then create the following similar measures in your original table, please note that there is no relationship between the new table and your original table.
salesMTD = TOTALMTD(SUM(Table6[Amount]),Table6[Date])
salesYesterday = CALCULATE(SUM(Table6[Amount]),FILTER(Table6,Table6[Date]=TODAY()-1))
selected measure1 = IF(HASONEVALUE(Table7[Measure]),SWITCH(FIRSTNONBLANK(Table7[Measure],Table7[Measure]),"Yesterday",[salesYesterday],"MTD",[salesMTD]),blank())
Reference:http://breaking-bi.blogspot.sg/2016/06/power-bipowerpivot-using-slicers-to.html
Regards,
Lydia Zhang
