Forum Discussion
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/2023 | 20/02/2024 | on going |
| 4 | HGT | 05/05/2023 | 10/10/2023 | done |
| 5 | KJR | 10/10/2023 | 12/12/2023 | on going |
I want to count the number of days from the end date column with today's date with conditions
< 30 days from Today - color yellow
> today - color red
Done - color black
I've tried with datediff but it hasn't worked.
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.
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.
10 Replies
Hi esuryahadi ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can 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" )(3) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
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.
Thank you.
This my data.
Sorry i try to attactment file csv and pbix is fail.
No WBS Index Title No Contract Total USD Start Date End Date Status C8000006 System WTP 3456 $3,45 21/10/2019 23/10/2019 Done C8000007 Constrution WTP 2987 $1,25 21/10/2020 23/10/2021 Done C8000008 Upgrading Submarine Hose 876 $1,25 01/10/2023 02/11/2023 On Going C8000009 Main Gathering Station 7409 $0,13 30/10/2023 01/11/2023 On Going C8000010 Upgrading Ring Main 1928 $0,17 24/02/2020 27/02/2020 Done C8000011 Install New Oil Trunkline 23 $0,14 02/10/2023 01/12/2023 On Going C8000012 System Cathodic Protection 923 $0,41 01/10/2023 01/12/2023 On Going C8000013 Intertank Pump 600 92 $0,06 19/09/2023 20/10/2023 On Going C8000014 Handheld Oil Analyzer 8634 $104,32 20/10/2023 03/01/2024 On Going No WBS Index Title No Contract Total USD Start Date End Date Status C8000006 System WTP 3456 $3,45 ######## ######## Done C8000007 Constrution WTP 2987 $1,25 ######## ######## Done C8000008 Upgrading Submarine Hose 876 $1,25 ######## ######## On Going C8000009 Main Gathering Station 7409 $0,13 ######## ######## On Going C8000010 Upgrading Ring Main 1928 $0,17 ######## ######## Done C8000011 Install New Oil Trunkline 23 $0,14 ######## ######## On Going C8000012 System Cathodic Protection 923 $0,41 ######## ######## On Going C8000013 Intertank Pump 600 92 $0,06 ######## ######## On Going C8000014 Handheld Oil Analyzer 8634 $104,32 ######## ######## On Going 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
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.
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.
https://1drv.ms/u/c/d028217ff837ea33/EQYIRuZSwlBPsfD1cqivFbwBiZCDTLvum99jjMIbsofnKA?e=CHNhaB
I try to upload pbxi, i hope you can help me
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.
