cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Wedding55
Helper II
Helper II

Different Colors Datesbetween Measure

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.
Chart.JPG
Best Regards
 
 
 
 

 

 

1 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)

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

10 REPLIES 10
Wedding55
Helper II
Helper II

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.

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)

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Thanks for your Help. Its working 🙂

Wedding55
Helper II
Helper II

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

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?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



v-zhenbw-msft
Community Support
Community Support

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)

 

dif1.jpg

 

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))

 

dif2.jpg

 

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.

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
Super User III
Super User III

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?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix ,

 

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

 Chart2.JPG

 

Thanks in advance.

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:

 

MFelix_0-1602858472540.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors