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'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.

esuryahadi's avatar
esuryahadi
Helper I
2 years ago

Hi Neeko Tang,

Sorry, you right.

I was wrong in explaining. 

What I mean is 

colume end date < 30 days from today - yellow and count how many IDContract

colume end date > from Today - Red and count how many IDContract

colume status done - black and count how many IDContract.

 

I hope you can understand.

Thank you very much so far your help

 

Regads,

Eko

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi esuryahadi ,

     

    You need to create another measure.

    Count = SWITCH(TRUE(),
ISFILTERED('Slicer'[Type])=FALSE(),COUNTROWS(ALLSELECTED('Table')),
SELECTEDVALUE('Slicer'[Type])="< 30 day",COUNTROWS(FILTER(ALLSELECTED('Table'),[conditions]="yellow")),
SELECTEDVALUE('Slicer'[Type])="> Today" ,COUNTROWS(FILTER(ALLSELECTED('Table'),[conditions]="red")),
SELECTEDVALUE('Slicer'[Type])="Done/Complete",COUNTROWS(FILTER(ALLSELECTED('Table'),[conditions]="black")))

    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.