Need Help in DAX

[Anonymous]

Hello Everyone, 

Start Date	Target Date	Status	% Progress
1-Oct-19	6-Jul-20	In Progress	50%
15-Jan-20	10-Aug-20	In Progress	30%
1-Dec-19	14-Jun-20	In Progress	60%
1-Feb-20	15-Apr-20	In Progress	70%
1-Feb-20	31-Mar-20	In Progress	10%
1-Jan-20	28-Feb-20	In Progress	85%
1-Feb-20	31-Mar-20	In Progress	10%
1-Jan-20	6-Jul-20	In Progress	50%
1-Nov-19	15-Apr-20	In Progress	70%
1-Nov-19	28-Feb-20	In Progress	60%
27-Jan-20	21-Feb-20	In Progress	80%
24-Feb-20	20-Mar-20	Not Started	0%
23-Mar-20	10-Apr-20	Not Started	0%
27-Jan-20	14-Feb-20	Completed	100%
1-Jan-20	28-Feb-20	In Progress	57%
1-Jan-20	28-Feb-20	In Progress	76%
1-Feb-20	28-Feb-20	In Progress	80%

If Target date is less then the  Today date < 15 days then project progress Percentage should be in light red color

If Target date is greater than Today date > 1 day the Project progress percentage should be in dark red color

If target date is less than Today date < 30 days then project progress percentage should be in yellow color

If Target date is  equal or today date and status is completed then percentage should be in green color.

Could someone help me with the DAX Query

[sturlaws]

Hi @Anonymous 

you can do this by using the conditional formatting option in e.g. a table visual. The formatting rules you described does not cover all possibilities, I think, but this will help you on the way.

First create a measure like this

FormattingMeasure =
AVERAGEX (
    'Table';
    IF (
        'Table'[Status] = "Completed";
        0;
        DATEDIFF ( TODAY (); 'Table'[Target Date]; DAY )
    )
)

Now on the [Progress]-field in the values area of your table, right click and select 'conditional formatting':

Select which option is more appropriate for your case(background or font color). After that, choose Format by rules, and add the rules, making use of the measure above:

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Pragati11]

Hi @Anonymous ,

Create a column having todays date in it as follows:

                                                                                   TodaysDate = Today()

Then use conditional formatting on your table visual. You can refer following link to see how conditional formatting is done in Power BI:

https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting

If this helps please give Kudos and mark it as a Solution! 🙂

Thanks,

Pragati