Different Colors Datesbetween Measure

[Wedding55]

Hello,

i have four measures to calculate the different financial years. The measures are the same, only the time horizon is different.

Days 20/21 = CALCULATE(SUM('Process Data'[Tage]), DATESBETWEEN('Date'[Date].[Date],"01.07.2020","30.06.2021"))

And i visaulize the 4 measure in a chart (see screenshot). In the data for the current financial year, i have every month the data for the next two month. In this example the data from july-september is fix (Ist) and the Data for october and november is a forecast. When i upload the data in november for october. The data from july-october is fix and the data from november and dezember is a forecast. And so on....

I want to highlight the forecast data in a different color automatically and not every month by myself.

Is this possible? Do you have an idea? It would be great. Thanks in advance.

Best Regards

[Wedding55]

Hi @MFelix 

i tried your measure in the test file. The measure is working (see table on the right side. For December there is "1".

But the colors in the chart are not changing. Can you see what is wrong?

Here is the Link to the file.

https://www.dropbox.com/s/lg8avqqabjahyrd/Test.pbix?dl=0

The check for higher than today is ok. Maybe i can change it to Max-Date - 2 because we only have data for 2 more months than the fixed data.

[MFelix · Accepted solution]

Hi @Wedding55 ,

My mistake since you are using months (that aggregate more than one date) you need to use the MAX instead of a selectedvalue:

Condittional Formatting = IF(MAX(Tabelle[Date]) >= TODAY();1;0)

[Wedding55]

Thanks for your Help. Its working 🙂

[Wedding55]

Hi @MFelix @v-zhenbw-msft 

Please excuse the delayed answer. I wasn`t in the office.

Its not the exaxt thing im searching for. I uploaded the test file on dropbox. See Link. I hope it works.

https://www.dropbox.com/s/jd6gpquzgppqy89/Test.pbix?dl=0

You can see there is Data until december 2020. But normally i update the file after each month. In this case the data until october is fix and the data for november and december is the forecast. Next month (in december) i update the file with the fixed november data and the data for december and january is the forecast. And the forecat i need the mark in different colors.

Is this possible? Thanks in advance 🙂

Best Regards

[MFelix]

Hi @Wedding55 ,

The solution I provide check if the date is higher than today and if yes returns 1 otherwise 0 then you can use this to make the condittional formatting.

Only question here is if you want to compare with todays date or with the updated date of the file but the principle is the same.

How do you determine if that particular month is actual or not by the updated date or today's date?

[v-zhenbw-msft]

Hi @Wedding55 ,

Are the current data and the forecast data from the same column in the table?

If yes, we can create a calculate column and two measures to meet your requirement.

1. Create a column to determine whether the current date belongs to the forecast month or the existing month.

column = 
var _today_month = MONTH(TODAY())
var _date_month = MONTH('Table'[Date])
var _today_year = YEAR(TODAY())
var _date_year = YEAR('Table'[Date])
return
IF(
    _today_month<_date_month&&_today_year>=_date_year,1,0)

2. Then we can create two measures, put them in Values.

Current value = 
CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[column]=0))

Forecast value = 
CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[column]=1))

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[v-zhenbw-msft]

Hi @Wedding55 ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @Wedding55 ,

Not really understanding what is the colour change that you need to have. What is the information you want to highlight, is it part of one of the lines is it the bar chart?

[Wedding55]

Hi @MFelix ,

i want to highlight automatically the last two month in the bars (see Screenshot).

 

Thanks in advance.

[MFelix]

Hi @Wedding55 ,

Don't know how you have your model setup in terms of dates and I assume you want to check if the date is higher than today then is forecast. Add the following code:

Condittional formatting = IF(SELECTEDVALUE('Table'[Date]) >= TODAY(); 1 ;0)

 Now just setup the condittional formatting on the bar part: