Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
JCK2
Helper III
Helper III

Compare dates from two different tables

Hello Power BI Champions!!

 

I am trying to compare two dates from two tables and the relationship between the columns is active.

 

I wrote the following calculation to get 1 and 0.

 

Column=

If (Table1[Ticket Close Date]> Related Table2 [End of month],1,0)

 

but Im not getting 1 as a result? where am i going wrong?

 

* Also what will be the calculation, if the relationship is not active?  Thanks a lot!!

Table - 1                                                                                               Table-2

Ticket Close Date End of month
22/Sept/202031/08/2020
  
6 REPLIES 6
vanessafvg
Super User
Super User

if the relationship is not active, you need to use the userelationship() function to force the measure you want to look at a specific date.

more information here.
https://docs.microsoft.com/en-us/dax/userelationship-function-dax

https://www.youtube.com/watch?v=sONvctPlplY

however looking at your code, what are you joining on relationship wise.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@vanessafvg  I am not joining anything!

 

I am trying to find out, which date is greater from the both tables and if its greater give me 1 else 0. 

 

Thanks!

why are you creating a column rather than a measures? ok if there is no relationship between the tables - what is the purpose of what you are doing so i can understand better, what is your objective?

if you are just trying to get the max date from each table

ie.
isgreater =
VAR maxtable1 =
CALCULATE ( MAX ( table1[date] ) )
VAR maxtable2 =
CALCULATE ( MAX ( table2[date] ) )
RETURN
IF ( maxtable1 > maxtable2, 1, 0 )






If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




My purpose is to din out;

a) Ticket open at the end of each month

b) also want to know, what was the status of that ticket last month.

For eg: I have a ticket which is opened in 1st July and closed on 25th Sept.

Ticket Status will be

end of month July- Open

end of month Aug-Open

end of month Sep- Closed

I want to see the status of ticket per month as it is, till it get closed. if i pull the report in dec, it should show me ( July= Open, Aug= Open and Sept = Closed). I use date ticket closed to determine open and close.

To achieve this, I am brining an external table, called end of month, which is formatted and has all dates similar to my date tables and want to use the blow logic.

If date closed> end of month ( different table) || - this wil bring all the closed one.

Isblank date closed- to check current status &&

date opened<= end of month,1,0) - to limit my ticket to less than end of month.

End of monthCondtion 1Condition 2Condition 3Status
July111Open
Aug111Open
Seven001Closed
Oct001Closed

My purpose is to find out the

Tickets open at the end of each month.

Also want to know, what was the status of that ticket last month.

For eg;

I have a ticket, which is opened in 1st July and closed on 25th September.

For the month of 31st July the status is open.

For the month of August the status is open.

But for the month of September it is closed.

So I want to see the status of ticket per month as it is, till it get closed. (In this case July-Open, Aug-Open), If I pull a report in December, I still want to see the same status.

I am using the date closed to identify if a ticket is open or closed.

To achieve this, I am brining an external table, called of end of month, which is formatted and has all dates similar to my date closed table… from 2018 and want to use the below logic ( don’t know if it works ☹)

If date closed > end of the month (external table) – ( This will bring the closed ones also)

|| isblank date closed – check the status

&& date opened<= end of month, 1,0 ( This will limit my ticket to less than end of month)

My purpose is to find out the

Tickets open at the end of each month.

Also want to know, what was the status of that ticket last month.

For eg;

I have a ticket, which is opened in 1st July and closed on 25th September.

For the month of 31st July the status is open.

For the month of August the status is open.

But for the month of September it is closed.

So I want to see the status of ticket per month as it is, till it get closed. (In this case July-Open, Aug-Open), If I pull a report in December, I still want to see the same status.

I am using the date closed to identify if a ticket is open or closed.

To achieve this, I am brining an external table, called of end of month, which is formatted and has all dates similar to my date closed table… from 2018 and want to use the below logic ( don’t know if it works ☹)

If date closed > end of the month (external table) – ( This will bring the closed ones also)

|| isblank date closed – check the status

&& date opened<= end of month, 1,0 ( This will limit my ticket to less than end of month)

End of Month

Condition 1

Condition 2

&& Condition 3

Status

July

1

1

1

Open

August

111

Open

September0

0

1

Closed

October

00

1

Closed

I will use end of month date as a filter 😊

My purpose is to find out the

Tickets open at the end of each month.

Also want to know, what was the status of that ticket last month.

For eg;

I have a ticket, which is opened in 1st July and closed on 25th September.

For the month of 31st July the status is open.

For the month of August the status is open.

But for the month of September it is closed.

So I want to see the status of ticket per month as it is, till it get closed. (In this case July-Open, Aug-Open), If I pull a report in December, I still want to see the same status.

I am using the date closed to identify if a ticket is open or closed.

To achieve this, I am brining an external table, called of end of month, which is formatted and has all dates similar to my date closed table… from 2018 and want to use the below logic ( don’t know if it works ☹)

If date closed > end of the month (external table) – ( This will bring the closed ones also)

|| isblank date closed – check the status

&& date opened<= end of month, 1,0 ( This will limit my ticket to less than end of month)

I will use end of month date as a filter 😊

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.