Forum Discussion
Helper Idue date filter
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.
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.
- esuryahadi
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 - esuryahadi
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.
- esuryahadi
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
