Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Need Help in DAX

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

DAX

2 Replies

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    6 years ago

    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

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    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.