Forum Discussion
Selectedvalue on date slicer
Hey good people of power BI family,
I am using a date slicer to generate two pie charts - one for the current month and one for the previous month.
The date slicer is based on my date table, of which the date field is related to my fact table, using a 1-* cardinality.
The problem is that currently, I have to choose two consectuive months from the slicer and then on one of the visuals, I have to use an additional filter to choose M-1 (e.g., I have to force-select the month as Jun, by adding month as a filter, if the current month is Jul).
If I try to use SELECTEDVALUE as a function within the measure to compute the MTD sales - that does not work and the visuals are broken.
Is there a fix for this? all I want to acheive is choose any month from the slicer and the visuals (the two pie charts in this case), should automatically update to the month Selected and the month previous to that?
I am attaching the screengrab below :
visual with slicer
Any help much appreciated
Hi monojchakrab ,
I created some data:
Here are the steps you can follow：
1. Create calculated table.
Slicer = DISTINCT('Table'[Date])2. Create measure.
Flag = var _select=SELECTEDVALUE('Slicer'[Date]) var _selectmonth=MONTH(_select) return IF( MONTH(MAX('Table'[Date]))=_selectmonth,1,0)Flag2 = var _select=SELECTEDVALUE('Slicer'[Date]) var _selectmonth=MONTH(_select) return IF( MONTH(MAX('Table'[Date]))=_selectmonth-1,1,0)3. Place [Flag] and [Flag1] on Viusal's Filter respectively
4. Result:
Select a date, one shows the data of the current month, and one shows the data of the previous month
If you need pbix, please click here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Anonymous - Thanks and let me try this out once. I will write you a feedback. Looks like a plausible workaround for sure
6 Replies
Hi monojchakrab ,
I created some data:
Here are the steps you can follow：
1. Create calculated table.
Slicer = DISTINCT('Table'[Date])2. Create measure.
Flag = var _select=SELECTEDVALUE('Slicer'[Date]) var _selectmonth=MONTH(_select) return IF( MONTH(MAX('Table'[Date]))=_selectmonth,1,0)Flag2 = var _select=SELECTEDVALUE('Slicer'[Date]) var _selectmonth=MONTH(_select) return IF( MONTH(MAX('Table'[Date]))=_selectmonth-1,1,0)3. Place [Flag] and [Flag1] on Viusal's Filter respectively
4. Result:
Select a date, one shows the data of the current month, and one shows the data of the previous month
If you need pbix, please click here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Anonymous - Thanks and let me try this out once. I will write you a feedback. Looks like a plausible workaround for sure
That worked like a charm Anonymous . Thanks for the great solution. The sample PBIX file was also most helpful
Hi Anonymous
I tried your solution on changing visuals to MTD and MTD-1 using the date slicer, basis selectedvalue on the same.
Can you help me understand the code a little better?
1. When I am chossing jul on the slicer, of course the max([date]) returns Jul and hence the check with the date selected on the slicer, trues out and hence the flag returns 1 as the value
2. What I am not able to understand is why would this work when the date selected on the slicer is say, Jun, which is <> Max([Date]) - in this case, the flag will return zero, but the filter on the visual is set at Flag=1; but the visual still returns the data for Jun correctly. This is the part I am unable to understand.
Any help appreciated and it will probably help better understand the code you have so elegantly used to solve my problem.
monojchakrab SELECTEDVALUE will not work because if there is more than one row returned it returns BLANK. Youse MAX, MAXX and MIN, MINX instead to get the limits and then you can use MONTH and YEAR functions to get the YEAR and MONTH of those values.
Hey Greg_Deckler - Thanks for leg-up!
I actually dont want to choose the dual values from the slicer; I am forced to do that bcos, otherwise I am not able to show the two visuals side by side.
So if I choose a different set of months, I have to physically change the filter on those visuals.
I want to choose one single month only and thru' suitable measures adjust the visuals to reflect MTD and MTD-1.
Here are two measures I wrote (but have not been able to make these work with Selectedvalue) :
1. MTD :
MTD sales = CALCULATE( 'Measures tray'[Total Sales],DATESMTD('Date Table'[Date]) )2. MTD-1 :
MTD M-1 = CALCULATE('Measures tray'[MTD sales],DATEADD('Date Table'[Date],-1,MONTH) )Could you help me re-write these two measures suitably, to take into account the name of the month chosen on the date slicer?
Really appreciate the help here
