Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Adnan Khan]

hello,
I am new to power bi . I am stuck here. I need to calculate measure . if depart_date is within BookingStart and BookingEnd date mark as “issue “other wise “no issue”

in other word if depart _date> Bookingstart and depart_date < BookingEnd then issue else no issue.

——————————
Adnan Khan
ANALYST
ETOBICOKE ON
——————————

[Christopher Schnaars]

Adnan:

You want a calculated column, but I see a potential issue: It looks like the time value of your depart date is always midnight. My guess is you want to look at everything as date, but you might need to adjust this formula or let us know if you need more help.
new_column_name = IF([depart_date].[Date] >= [BookingStart].[Date] && [depart_date].[Date] <= [BookingEnd].[Date],”issue”,”no issue”)

——————————
Christopher Schnaars
Laboratory Testing Inc.
Hatfield, Pennsylvania
——————————
——————————————-

[Adnan Khan]

hello Christopher ,
Thank you very much for your reply. I want to mention that Depart_date is in separate table and BookingStart and BookingEnd date in separate table. I joined the relationship. In second image is visual table where I want to put condition.  How can i calculate column if it is coming from different tables.  please note lots of BookingEnd date is empty.

I tried  your code but did not work.

——————————
Adnan Khan
ANALYST
ETOBICOKE ON
——————————
——————————————-

[Christopher Schnaars]

Hi, Adnan:

What fields are you using to join the two tables? And is it a one-to-one relationship? In other words, does every row in VOR have exactly one related row in LOAD and vice versa?

You could try something like this:
new_column_name = IF(VOR[DEPART_DATE].[Date] >= MIN(LOAD[BookingStart].[Date]) && VOR[DEPART_DATE].[Date] <= MAX(LOAD[BookingEnd].[Date]),”issue”,”no issue”)

You could use ISBLANK() to extend the logic for cases where BookingEnd is empty.

——————————
Christopher Schnaars
Laboratory Testing Inc.
Hatfield, Pennsylvania
——————————
——————————————-

[Brian Renfrow]

Christopher
How would I add the ISBLANK() to this so it would add no when the finish date is blank.

PM ON TIME test = IF(WORKORDER[USAMS SCHEDFINISH].[Date]>=WORKORDER[USAMS ACTFINISH].[Date]&&WORKORDER[USAMS ACTFINISH].[Date]<=WORKORDER[USAMS SCHEDFINISH].[Date],“yes”,“no”)

Thank you

——————————
Brian Renfrow
Maintenance Planer
——————————
——————————————-

[Alberto Gastaldo]

Hello Adnan,

what you need is more a calculated column than a measure.

If you add a calculated column to your table, yo can write formula like this

= IF ( depart _date> Bookingstart && depart_date < BookingEnd ; “issue” ; “no issue”

and call the new column “hasissue”

This is a column that you can use in your grid.

You need a measure only if you want to know how many issues you have (for example in a period of time or of a customer etc…).

In that case you can write a measure like this

#issues = CALCULATE ( COUNTROWS(<yourtable>); FILTER ( <yourtable> ; hasIssue = “issue”) )

Let me know.

——————————
Alberto Gastaldo
SQL Server & BU sernior consultant and trainer
+393471609784
——————————
——————————————-