Dynamic column values based on selected date. Need help

[ignas]

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

[v-jiascu-msft · Accepted solution]

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

[ignas]

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

[v-jiascu-msft]

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

[ignas]

Hey @v-jiascu-msft

Thank you for the response.

Yes of course I need different colors:

We need to know when ACT finished and when BUD/FC starts.

[v-jiascu-msft]

Hi @ignas,

As a workaround, we can change the measure and put it in the field "Color saturation".

Scenario+ = 
    IF ( MAX ( DimDate[Datekey] ) <= IF ( HASONEVALUE ( 'Table8'[Date] ), MIN ( 'Table8'[Date] ),"Actual"), 1, 0 )

Best Regards,

Dale

[ignas]

Updated link which did not work: https://mega.nz/#!LrpRnSwR!DU_Bir3jYPSU139nSPgsbRuTKsJCeqozj6eKDrAhzbo