Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Conditional formatting based on time period

I have few questions on DAX conditional formatting.

There is a KPI whose target is 10% for the year jan 2020 - mar 2021, now the target has been revised to 15% for from apr - 2021, how do I write a specific DAX for conditional formatting based on time period.

Currently am using the the Icon based conditional formatting for the year 2020, now since the target has changed when I override the conditional formatting based on apr 2021 target, the conditional formatting of 2020 is being changed too, which am trying to avoid.

Can someone help me on DAX where in i can use icon based conditional formatting for two different targets based on time period mentioned above

1 ACCEPTED SOLUTION

Hi @Anonymous ,

The following setting is to put the year and month together into the following format: 202001, 202002, xxx etc, and convert them into numerical values. If the month number less than 10 and you didn't make any adjustment(

CONCATENATE ( "0", _month ) ), it will become 20201, 20202 and so on. In this case, it is inconvenient for the subsequent numerical comparison.
VAR _selymonth =
    VALUE (
        CONCATENATE ( _year, IF ( _month < 10, CONCATENATE ( "0", _month ), _month ) )
    )

The condition "SELECTEDVALUE ('Table'[achieved]) >= _target * 0.98 " is to judge whether the achieved value reaches 98% or more of the target value.

I updated the sample pbix file base on your provided date table, please find the attachment for the details.

yingyinr_0-1619163139015.png

If the above one is not applicable for your scenario, it is better to share your sample pbix file(exclude sensitive data) with me in order to provide you a suitable solution...

Best Regards

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

View solution in original post

14 REPLIES 14
v-yiruan-msft
Community Support
Community Support

Hi @Anonymous ,

You can refer the content in the following links to achieve it:

Conditional Formatting based on Time for multiple conditions

Custom Conditional Formatting Techniques In Power BI

If the above one is not applicable for your scenario, please provide some sample data(exclude sensitive data) and  your expected result with samples and function details. Thank you.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-yiruan-msft 

 

am trying to achieve above type of icon conditional formatting, since the target was changed from april 2021, am not able to amend it in my matrix table to new targets as the old targets are also reflecting per new when i try to amend it in icon conditional formatting, therefore am looking for a dax which takes the conditional formatting based on time period ( jan,feb etc)...In my scenario my target was same from jan 2020 to mar 2021, it changed from apr 2021.

 

i want to show icon conditional formatting in same table for two different targets

 

data.JPGicon.JPG

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

yingyinr_0-1619077684269.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-yiruan-msft 

 

Thank you for your expertise.

 

Just few small changes, so in my matrix table there would be no target column and there would be no month column

 

Month i'll select from the slicer and target is already aware by operations.

so when i select for instance march 2021, the formatting should be based on 10%, when i select april 2021 the formatting should be based on 15% in the same table. since here formatting is changing based on months how should i amend this factor in my conditional formatting
my tager was 10% from Jan 2020 to Mar 2021, from Apr 2021 it has changed to 15%

In monthly slicer whichever month i select formatting should reflect accordingly.

 

Please share your thoughts on this

Anonymous
Not applicable

Along with red and green there is also amber in my RAG 

Hi @Anonymous ,

I updated my sample pbix file(see attachment), please check whether it can achieve your requirement.

yingyinr_0-1619080006262.png

Could you please explain that which situation it will display red, green, amber icon separately in?

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-yiruan-msft 

 

Thank you for the update.

 

Green is on target, amber is 2% from target and red is >2% from target, please update

Hi @Anonymous ,

I updated the sample pbix file base on your requirement, please find it for the details.

yingyinr_1-1619082859962.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-yiruan-msft 

 

Really appreciate your quick solution,Thanks for all your help.

 

I just have few questions in DAX , just wanted to clarify so that i can use with different combinations whenever need be

1)could you please help me und why did we take month <10 and o in selymonth variable
2) why are we multipying with 0.98?snip.JPG

Hi @Anonymous ,

The following setting is to put the year and month together into the following format: 202001, 202002, xxx etc, and convert them into numerical values. If the month number less than 10 and you didn't make any adjustment(

CONCATENATE ( "0", _month ) ), it will become 20201, 20202 and so on. In this case, it is inconvenient for the subsequent numerical comparison.
VAR _selymonth =
    VALUE (
        CONCATENATE ( _year, IF ( _month < 10, CONCATENATE ( "0", _month ), _month ) )
    )

The condition "SELECTEDVALUE ('Table'[achieved]) >= _target * 0.98 " is to judge whether the achieved value reaches 98% or more of the target value.

I updated the sample pbix file base on your provided date table, please find the attachment for the details.

yingyinr_0-1619163139015.png

If the above one is not applicable for your scenario, it is better to share your sample pbix file(exclude sensitive data) with me in order to provide you a suitable solution...

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-yiruan-msft 

 

Thank you for the update, i have used the below dax var measure for monthly and its working for me, am trying to amend the same for weekly too is there a way where i can use the weekly column and index column in the same variable of below, because the month and week would be in a slicer mode and kpi numbers in a matrix table.
when i select the month in slicer conditional formatting is working fine as per below variable, but when i select a week  it isnt working.


 

I need to combine the month and week var measure in one dax beacuse the matrix table shows both the monthly and weekly in one single table, could you please tell me a way where i can amend both week and month in same variable measure in the cal.JPGw.JPGbelow, so that whenever i select week/month in slicer the formatting should be aligned.

 

VAR _TARGET = IF(
SELECTEDVALUE('CX MONTH'[Index]) >=0 && SELECTEDVALUE('CX MONTH'[Index])<=7 ,0.10,
IF(SELECTEDVALUE('CX MONTH'[Index])>=8, 0.12, BLANK())
)
RETURN
IF(
[achieved measure] >= _TARGET,1,
IF([achieved measure]>= _TARGET*0.98, 2,3))

Anonymous
Not applicable

Hi @v-yiruan-msft 

 

any update on this please, i could achieve the result partially.kindly help for complete resolution

 

Hi @Anonymous ,

Could you please share your sample pbix file with me in order to make troubleshooting and provide a suitable solution for your scenario? Because I don't know that how did you set the visualizations and is there any relationship be created among these tables(month table, week table and fact tables etc)...

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-yiruan-msft 

 

i have used the below dax var measure for monthly and its working for me, am trying to amend the same for weekly too is there a way where i can use the weekly column and index column in the same variable of below, because the month and week would be in a slicer mode and kpi numbers in a matrix table.
when i select the month in slicer conditional formatting is working fine as per below variable, but when i select a week  it isnt working.


 

I need to combine the month and week var measure in one dax beacuse the matrix table shows both the monthly and weekly in one single table, could you please tell me a way where i can amend both week and month in same variable measure in thebelow, so that whenever i select week/month in slicer the formatting should be aligned.

 

VAR _TARGET = IF(
SELECTEDVALUE('CX MONTH'[Index]) >=0 && SELECTEDVALUE('CX MONTH'[Index])<=7 ,0.10,
IF(SELECTEDVALUE('CX MONTH'[Index])>=8, 0.12, BLANK())
)
RETURN
IF(
[achieved measure] >= _TARGET,1,
IF([achieved measure]>= _TARGET*0.98, 2,3))

 

I have created a relationship for months and weekly table with the main data table, now i would like to write a dax for week and month in one single dax as the matrix table of kpi consist of both monthly and weekly data, below are the monthly and weekly tables that i created for relationship with main data table

cal.JPGw.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.