due date filter

[esuryahadi]

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.

[Anonymous · Accepted solution]

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 · Accepted solution]

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. 

[esuryahadi]

https://1drv.ms/u/c/d028217ff837ea33/EQYIRuZSwlBPsfD1cqivFbwBiZCDTLvum99jjMIbsofnKA?e=CHNhaB 

I try to upload pbxi, i hope you can help me

[Anonymous]

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. 

[Anonymous]

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]

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 · Accepted solution]

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]

Thank you very much you help.

[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

[Anonymous]

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]

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