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.

esuryahadi's avatar
esuryahadi
Helper I
2 years ago

Hi, Sorry i have problem about my data.

But so far my data is same.

after i try you code, still have wrong 

in IDContract 3 and 5 shouldn't not red. 

and i want to visual like pic 

 

Very sorry if my Language is bad

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. 

  • esuryahadi's avatar
    esuryahadi
    Helper I
    2 years ago

    Thank you very much you help.