Forum Discussion

esuryahadi's avatar
esuryahadi
Helper I
2 years ago
Solved

due date filter

Hello, I have table data as follows. IDContract  Name Contract start date End Date Status 1 ADB 22/02/2023 21/04/2023 done 2 DFG 01/06/2023 02/10/2023 on going 3 DFR 19/12...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi esuryahadi ,

     

    You can also create a measure.

    conditions = SWITCH(TRUE(),
MAX('Table'[Status])="done","black",
MAX('Table'[End Date]) > TODAY(),"red",
DATEDIFF(MAX('Table'[End Date]),TODAY(),DAY)<30,"yellow"
)

    Setting the Conditional Format.

     

    Best Regards,

    Neeko Tang

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

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi esuryahadi ,

     

    I'm sorry I don't quite understand, the IDContract is 3 and 5 when the end date is 2024/2/20 and 2023/12/12. today's date is 2023/11/3 and the end date is greater than today, shouldn't he show red?

     

    According to your description,

    (1) We can create a table.

    Slicer = DATATABLE ( 
    "Type", STRING,
    {
        { "< 30 day"},
        { "> Today" },
        { "Done/Complete" }
    }
)

    (2) We can create measures. 

    conditions = SWITCH(TRUE(),
MAX('Table'[Status])="done","black",
MAX('Table'[End Date]) > TODAY(),"red",
DATEDIFF(MAX('Table'[End Date]),TODAY(),DAY)<30,"yellow"
)
    Flag = SWITCH(TRUE(),
ISFILTERED('Slicer'[Type])=FALSE(),1,
SELECTEDVALUE('Slicer'[Type])="< 30 day" && [conditions]="yellow",1,
SELECTEDVALUE('Slicer'[Type])="> Today" && [conditions]="red",1,
SELECTEDVALUE('Slicer'[Type])="Done/Complete" && [conditions]="black",1,0)

    (3) Setting the conditional format and filtering [Flag=1] as a visual object.

    Best Regards,

    Neeko Tang

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi esuryahadi ,

 

I can't access the link you shared, it seems to have no permissions. Please re-share it.


Referring to my second reply, you can create the measure and then set the conditional format. Refer my pbix file.

May I ask if you have tried the code I provided? If the output does not match your expected results, please show your input data and expected results in detail.

 

 conditions = SWITCH(TRUE(),
MAX('YourTableName'[Status])="done","black",
MAX('YourTableName'[End Date]) > TODAY(),"red",
DATEDIFF(MAX('YourTableName'[End Date]),TODAY(),DAY)<30,"yellow"
)

 

 

Best Regards,

Neeko Tang

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