Need help with this logic

[ajitsahoo8338]

Hello Everyone, I am trying to create logic but failed to do. 
I need to create a Status Column. In which there should be rows/options i.e "On-Time", "Late", "Released"
"On-Time"= 

NOT(ISBLANK(Project_v2_w_LW_PPC[Release Date])) &&

    [Release Date] <= [Baseline_EndDate], "On-Time"
"Late"=

NOT(ISBLANK(Project_v2_w_LW_PPC[Release Date])) &&

    [Release Date] > [Baseline_EndDate] ||

    ISBLANK(Project_v2_w_LW_PPC[Release Date]) && [Baseline_EndDate] < TODAY(), "Late"
"Released"= 

NOT(ISBLANK(Project_v2_w_LW_PPC[Release Date])) &&

    [Release Date] <= [Baseline_EndDate || 

NOT(ISBLANK(Project_v2_w_LW_PPC[Release Date])) &&

    [Release Date] > [Baseline_EndDate]

(Basically, if any row has a Release Date Then It will be considered as Released)

The issue is with the Released logic because of overlapping conditions with On-Time and Late. How to write this codition correctly?

Thank You,
Ajit

[Kedar_Pande]

@ajitsahoo8338 You can try:

Status = 
SWITCH(
TRUE(),
NOT(ISBLANK(Project_v2_w_LW_PPC[Release Date])), "Released",
NOT(ISBLANK(Project_v2_w_LW_PPC[Release Date])) && Project_v2_w_LW_PPC[Release Date] <= Project_v2_w_LW_PPC[Baseline_EndDate], "On-Time",
(
ISBLANK(Project_v2_w_LW_PPC[Release Date]) && Project_v2_w_LW_PPC[Baseline_EndDate] < TODAY()
) || 
(
NOT(ISBLANK(Project_v2_w_LW_PPC[Release Date])) && Project_v2_w_LW_PPC[Release Date] > Project_v2_w_LW_PPC[Baseline_EndDate]
), "Late",

BLANK()
)

This structure ensures each row fits into exactly one category without overlap.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[ajitsahoo8338]

Hello @Kedar_Pande This is not working

[ajitsahoo8338]

Hello @Bibiano_Geraldo and @Uzi2019  

My data looks like this. I want to create a visual table like this and add a status slicer. From that slicer, If I select On-Time then it should show On-Time rows and For Late selection it should show Late and For Released Selection, it should show those rows which have a release date

[Uzi2019]

HI @ajitsahoo8338 

Can you share the sample data with expected output??

It would be better working with data instead of just a logic.

you can share the simple excel file screenshot for better understanding.

[ajitsahoo8338]

Hello @Uzi2019 

My data looks like this. I want to create a visual table like this and add a status slicer. From that slicer, If I select On-Time then it should show On-Time rows and For Late selection it should show Late and For Released Selection, it should show those rows which have a release date.

[Bibiano_Geraldo]

Hi,
Based on your table, you need to create two calculated columns and you will have to add two slicers to your report:

Column for released status:
ReleasedStatus =
IF(
NOT(ISBLANK(Project_v2_w_LW_PPC[Release Date])),
"Released",
BLANK()
)

Column for On time and Late Status:

OnTimeOrLate =
IF(
NOT(ISBLANK(Project_v2_w_LW_PPC[Release Date])) &&
Project_v2_w_LW_PPC[Release Date] <= Project_v2_w_LW_PPC[Baseline_EndDate],
"On-Time",
IF(
NOT(ISBLANK(Project_v2_w_LW_PPC[Release Date])) &&
Project_v2_w_LW_PPC[Release Date] > Project_v2_w_LW_PPC[Baseline_EndDate],
"Late",
IF(
ISBLANK(Project_v2_w_LW_PPC[Release Date]),
"Open",
BLANK()
)
)
)

[Uzi2019]

Hi @ajitsahoo8338 

Can you add one more staus column of Late/Release/On time. next to your table.???

so that will create logic accordingly..

[ajitsahoo8338]

My data looks like this. I want to create a visual table like this and add a status slicer. From that slicer, If I select On-Time then it should show On-Time rows and For Late selection it should show Late and For Released Selection, it should show those rows which have a release date

[ajitsahoo8338]

Project Number	Activity Number	Task Name	Resource	Baseline EndDate	Release Date
226	3000	Long Lead Release	MECHANICAL	8/16/2024 0:00	8/16/2024 0:00
227	3000	Long Lead Release	MECHANICAL	8/16/2024 0:00	8/16/2024 0:00
100	3000	Manuals Complete	TECH	6/18/2024 0:00	6/18/2024 0:00
227	3001	GA Tasks/Initial BOM	MECHANICAL	9/13/2024 0:00	9/20/2024 0:00
225	3002	GA Initial BOM Release	MECHANICAL	7/29/2024 0:00	8/16/2024 0:00
226	3002	BOM Release	MECHANICAL	9/13/2024 0:00
220	3002	Initial BOM	MECHANICAL	2/29/2024 0:00	3/29/2024 0:00
72	3002	l Comp Long Lead	MECHANICAL	5/31/2024 0:00	5/10/2024 0:00
131	3002	Paint Scheme	MECHANICAL	6/21/2024 0:00	5/2/2024 0:00
131	3003	Stop Kit	ELECTRICAL	5/31/2024 0:00	5/31/2024 0:00
225	3003	MA Group	MECHANICAL	9/17/2024 0:00
132	3004	Power Revision	ELECTRICAL	7/29/2024 0:00	7/30/2024 0:00

My data looks like this. I want to create a visual table like this and add a status slicer. From that slicer, If I select On-Time then it should show On-Time rows and For Late selection it should show Late and For Released Selection, it should show those rows which have a release date

[ajitsahoo8338]

Hello @Bibiano_Geraldo  Thank you for your response. After using your DAX I am only getting On-Time and Late. 

[Bibiano_Geraldo]

Could you please share a sample of your table? Please dont share sensitive data.

Note: The released status will only appear if all other status are not satisfied.

[ajitsahoo8338]

Hello @Bibiano_Geraldo 

My data looks like this. I want to create a visual table like this and add a status slicer. From that slicer, If I select On-Time then it should show On-Time rows and For Late selection,it should show Late and For Released Selection, it should show those rows which have a release date

[ajitsahoo8338]

Hello @Bibiano_Geraldo and @Uzi2019 My data looks like this. I want to create a visual table like this and add a status slicer. From that slicer, If I select On-Time then it should show On-Time rows and For Late selection it should show Late and For Released Selection, it should show those rows which have a release date.

[Uzi2019]

Hi @ajitsahoo8338 

Please write status in front of that table. like this

so will implement the logic accordingly.

[ajitsahoo8338]

Hello @Uzi2019 

In The Slicer, There should be there option to select. Released, on time and Late. If I select Released then it should show all On-Time rows and Late Rows which have a Release Date. For On-Time Selection, it should show all on-time rows and for late selection, it should show all late rows.

[ajitsahoo8338]

Hello @Uzi2019 

In The Slicer, There should be three option to select. Released, on time and Late. If I select Released then it should show all On-Time rows and Late Rows which have a Release Date. For On-Time Selection, it should show all on-time rows and for late selection, it should show all late rows.

[Bibiano_Geraldo]

Hi,

Try the following DAX:

If this answer help you, please make it as solution.

Status =
IF(
NOT(ISBLANK(Project_v2_w_LW_PPC[Release Date])) &&
Project_v2_w_LW_PPC[Release Date] <= Project_v2_w_LW_PPC[Baseline_EndDate],
"On-Time",
IF(
NOT(ISBLANK(Project_v2_w_LW_PPC[Release Date])) &&
Project_v2_w_LW_PPC[Release Date] > Project_v2_w_LW_PPC[Baseline_EndDate],
"Late",
IF(
ISBLANK(Project_v2_w_LW_PPC[Release Date]) &&
Project_v2_w_LW_PPC[Baseline_EndDate] < TODAY(),
"Late",
IF(
NOT(ISBLANK(Project_v2_w_LW_PPC[Release Date])),
"Released",
BLANK()
)
)
)
)

[ajitsahoo8338]

Hello @Bibiano_Geraldo  Thank you for your response. After using your DAX I am only getting On-Time and Late. 

[ajitsahoo8338]

Hello @Bibiano_Geraldo  After using you DAX. I am only geting Late and On-Time not Released.