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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ikezaki
Frequent Visitor

Problem with next day in power BI

Hi guys,

 

I need to compare two dates (Receiving date and Processed Date), what I need to check is if Processed date is equal or the next day of receiving date. For example if Receiving date is Jun 22, 2021 the Processed date should be Jun 22, 2021 or Jun 23, 2021. If not than its considered a fail.

Another issue I have I will need consider holidays and weekends when I comparing these two dates. Have you guys had any experience comparing dates like the example above?

 

Thank you in advance,

 

Eduardo

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @Ikezaki 

 

Do all receiving dates and processed dates happen on working dates? If so, you want to check whether a processed date is equal or the next working date of the receiving date? To achieve this goal, you need to have a column in Date table to mark whether a date is a working date. Then you can add a working date index column based on that.

 

For example, in my sample below, my working dates are all weekdays without weekends (ignoring other holidays). The number 1 represents it's a working day. Based on this, I add a WorkingDayIndex column to count the cumulative working dates in the Date table. This will be used to calculate the next working day later.

062403.jpg

 

Then I create below measure to check the dates.

Measure = 
VAR _receivingDateIndex = SELECTEDVALUE('DimDate'[WorkingDayIndex])
VAR _receivingDate = SELECTEDVALUE('Table'[Receiving date])
VAR _nextWorkingDate = MAXX(FILTER(ALL(DimDate),DimDate[WorkingDayIndex]=_receivingDateIndex+1),DimDate[Date])
VAR _processedDate = SELECTEDVALUE('Table'[Processed Date])
RETURN
IF(_processedDate=_receivingDate||_processedDate=_nextWorkingDate,"True","False")

062404.jpg

 

Please note that my sample is created based on the assumption that all receiving dates and processed dates should happen on working days. If your receiving and processed dates could happen on non-working dates, we need to do some modifications. And you need to create the [Is Working Day] column based on your weekends and holidays. Attached the pbix for your reference.

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

View solution in original post

9 REPLIES 9
v-jingzhang
Community Support
Community Support

Hi @Ikezaki 

 

Do all receiving dates and processed dates happen on working dates? If so, you want to check whether a processed date is equal or the next working date of the receiving date? To achieve this goal, you need to have a column in Date table to mark whether a date is a working date. Then you can add a working date index column based on that.

 

For example, in my sample below, my working dates are all weekdays without weekends (ignoring other holidays). The number 1 represents it's a working day. Based on this, I add a WorkingDayIndex column to count the cumulative working dates in the Date table. This will be used to calculate the next working day later.

062403.jpg

 

Then I create below measure to check the dates.

Measure = 
VAR _receivingDateIndex = SELECTEDVALUE('DimDate'[WorkingDayIndex])
VAR _receivingDate = SELECTEDVALUE('Table'[Receiving date])
VAR _nextWorkingDate = MAXX(FILTER(ALL(DimDate),DimDate[WorkingDayIndex]=_receivingDateIndex+1),DimDate[Date])
VAR _processedDate = SELECTEDVALUE('Table'[Processed Date])
RETURN
IF(_processedDate=_receivingDate||_processedDate=_nextWorkingDate,"True","False")

062404.jpg

 

Please note that my sample is created based on the assumption that all receiving dates and processed dates should happen on working days. If your receiving and processed dates could happen on non-working dates, we need to do some modifications. And you need to create the [Is Working Day] column based on your weekends and holidays. Attached the pbix for your reference.

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

Thank you for the swift solution for the issue, I have tried to replicate the exactly same steps as you showed me, the only difference between your model and the model I am working on is the Processed date is in a different table, the relationship between the received table and processed table is 1 to many to one as we processed the stock by batches. The relationship between the date table and the received table is the same as the one you shared.

Please see the example of another issue that happened using the Measure you suggested.

Ikezaki_0-1624566129421.png

The First date is the received date and second date is processed date, when I add the measure to check if the next working day is TRUE or FALSE, the table lose track of the dates, please see below:

Ikezaki_1-1624566280835.png

The Next Day check is exactly the same as the one posted above.

Next Day Check = 
VAR _receivingDateIndex = SELECTEDVALUE('Date Dimension'[WorkingDayIndex])
VAR _receivingDate = SELECTEDVALUE(Final_Merger[First Date])
VAR _nextWorkingDate = MAXX(FILTER(ALL('Date Dimension'),'Date Dimension'[WorkingDayIndex]=_receivingDateIndex+1),'Date Dimension'[Date])
VAR _processedDate = SELECTEDVALUE('Export Worksheet'[Second Date])
RETURN
IF(_processedDate=_receivingDate||_processedDate=_nextWorkingDate,"True","False")

Would you know what could be wrong on my model?

 

Thank you in advance once again

Hi @Ikezaki 

 

Which columns is the relationship between received table and processed table built on in your current model? I see a many-to-many relationship between Receipt_ID_Test and PRE-ADVICE-ID in the pbix file you shared. 

 

Jing

Hi Jingzhang,

 

Yes thats correct, the join between this tables are with receipt id and pre advice id however, we are still cleansing the data and the relationship might change to one to many (one pre advice id to many receipt id).

 

Thank you

Kumail
Post Prodigy
Post Prodigy

Hello @Ikezaki 

 

If you could provide sample .pbix file that should help provide quick work on the solution.

 

Regards

Kumail Raza

 

Hi @Kumail,

 

How can I attach the pbix here? I have tried and I got an error message.

 

Thank you

@Ikezaki you can insert the link from your google drive, onedrive or dropbox.

 

Hope this helps.

 

Regards

Kumail Raza

@Kumail Thank you Kumail, please see the link below. In this case I am trying to compare first date with the second date.

 

https://1drv.ms/u/s!Ak0DVagYmTvJzGTOQenzgVtQL9j0?e=b9PtnM

Hello @Ikezaki 

 

There is a many to many relationship between the tables with first and second date and therefore, we cannot get a single value for 1 date with the corresponding other table.

 

Either you can just clean the date to have 1 to many or many to 1 relationships between the 2 tables or combine the 2 data with related rows and send back. 

Kumail_0-1624540797937.png

 

I hope this helps and you can get back once you have additional query.

 

Regards

Kumail Raza

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.