Forum Discussion
Dynamic column values based on selected date. Need help
Hello
I have this data:
I want to have a date parameter. If I choose 01-04-2018 date then it would mean that all dates before 01-04-2018 are actual and all dates from 01-04-2018 are forecast. It looks simple, but I cannot get it working.
Please also have a look at my dashboard with data: https://mega.nz/#!LrpRnSwR!DU_Bir3jYPSU139nSPgsbRuTKsJCeqozj6eKDrAhzbo
Thanks a lot
Regards,
Ignas
Hi ignas,
The solution could be as follows.
1. Create an independent date table. Don't establish relationships with other tables.
2. Create a slicer.
3. Create a measure like below.
Measure 9 = VAR selectedDate = IF ( HASONEVALUE ( 'Table8'[Date] ), MIN ( 'Table8'[Date] ) ) RETURN IF ( MAX ( DimDate[Datekey] ) <= selectedDate, "Actual", "Forecast" )
Best Regards,Dale
6 Replies
Hi ignas,
The solution could be as follows.
1. Create an independent date table. Don't establish relationships with other tables.
2. Create a slicer.
3. Create a measure like below.
Measure 9 = VAR selectedDate = IF ( HASONEVALUE ( 'Table8'[Date] ), MIN ( 'Table8'[Date] ) ) RETURN IF ( MAX ( DimDate[Datekey] ) <= selectedDate, "Actual", "Forecast" )
Best Regards,Dale
Hello v-jiascu-msft
It really works very well. Thank you a lot
I have one issue though. Then I try to visualize with a graph it is not possible to display Actual/Forecast column in the graph. Do you know where the problem can be? I cannot add to Legend this newly created column.
Here is a new file: https://mega.nz/#!emIj2Z4A!BtaDt9WFN5KTlMRbM8awLMsqyHPWl7uh0SEDGA_eYic
Thanks a lot
Regards,Ignas
Hi Ignas,
I'm afraid we can't add a measure in the Legend. Actually, we don't need to do that in your scenario. Because every Axis value only has one Scenario. What't the result you expect?
Best Regards,
Dale
Updated link which did not work: https://mega.nz/#!LrpRnSwR!DU_Bir3jYPSU139nSPgsbRuTKsJCeqozj6eKDrAhzbo
