Date change query

[Pricey79]

Hello

I was hoping someone could help please

I have a calculated column that changes to yes or no depending on the values of other columns. Is it possible to get the date of which the calculated column changed from no to yes?

Thank you in advance

[Anonymous]

Hi @Pricey79 ,

According to your statement, I know that you have ABC1 and ABC2 in ABC, the status of ABC is based on ABC1 and ABC2, only both of them return "Yes", the status of ABC will return "Yes".

Now you may meet this situation, Category A, ABC1 may show "Yes" and ABC2 show "No". If ABC2 show "Yes" after refresh, you want to get the last modified date of ABC2.

We only could get last refresh date by DateTime.LocalNow() in Power Query. This function will show the last refresh datetime in all fields. However we may have this situation, Category B,both ABC1 and ABC2 has shown "Yes" before this refresh. But it will still show last refresh datetime instead of keep the datetime before. Then you will be confused whether this category is modified after this refresh. 

So I think Power BI now doesn't support you to achieve this.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

You can find out when your dataset was last refreshed (via the refresh history API) . That will have to be the date when the column was calculated. 

It's a bit pointless though - as the owner of that dataset you shouldknow when you changed what. 

[TheoC]

Hi @Pricey79 

You can create another Calculated Column to provide you with the output your after using an IF statement:

Yes Date = IF ( 'Table'[Yes or No Column] = "Yes" , 'Table'[Date] , BLANK() )

Hope this helps 🙂

Theo

[Pricey79]

@TheoC  Thank you for your reply, but what date would I put here?

"Yes" , 'Table'[Date] , BLANK() )

The two fields that feed the Yes/No column come from an external database that I do not control. I dont know if that helps you or not though. 

[TheoC]

Hi @Pricey79 

Do you have visibility of when the dates were changed? If not, and running off what @lbendlinhas raised, how frequently is the data refreshed?

The challenge you will have is that you'd need to maintain all records historically and combine them in a source given that a change from No to Yes may occur for every record (theoretically).

I apologise as I was under the incorrect impression that there was a Date column in your records to provide the context. If not, it has the potential to become a bit messy.

Apologies again!

Theo

[Pricey79]

@TheoC 

Thank you and apologies for not being clearer in the original post.

The external data source will contain on average 50-100 records at once, the whole pbix is refreshed 4 times a day.  Out of the 50-100 records, around 5-10 will change from no  to yes on a daily basis. What I'm after is to show when the record changed to yes, to show when it was ready for the next part of the chain.

Hope that makes more sense!

[TheoC]

Hi @Pricey79 

It makes a lot of sense. Just to clarify, there is no date / time column in your data?

Thanks,

Theo

[Pricey79]

@TheoC  there is a date field from the external data source.

Let me see if I can explain better.

In the external data there is something called ABC. This is split into two parts, ABC1 and ABC2.

ABC1 and ABC2 will be finished at different times, but the next part of the supply chain wont care untill they are both complete. When ABC1 and ABC2 are complete, that means ABC is complete and it can move long the chain.

In the data source , there is a date last modified field for ABC1 and ABC2.

ABC1 and ABC2 have a status field.

I have a calculated column that says if ABC1 AND ABC2 status shows complete, then show Yes, if not no.

Does that help?

I appreciate your time and help. 

[TheoC]

Hi @Pricey79 

Okay wonderful! Use the Date Last Modified column where I had the Date written in the original solution I put forward.  

That should get you the output you are after.

Theo

[Pricey79]

@TheoC 

But how do I differentiate between the last modfied date of ABC1 or ABC2?

ABC1 and ABC2 are brought in from two data sources. Sorry, I'm not an expert in this by any means!

[TheoC]

Hi @Pricey79 

Okay, you need to adjust the formula slightly. 

Date = IF ( AND ( 'ABC1'[Yes or No Column] = "Yes" ,'ABC2'[Yes or No Column] = "Yes" ) ,'Table'[Date] , BLANK() )

Also, sincere apologies for grammatical errors and syntax errors. I am on phone and it's quite challenging to write on mobile version of community lol.

[Pricey79]

@TheoC 

Sorry! What date would go in the last part though?

[TheoC]

@Pricey79 The Date Last Modified column.

[Pricey79]

@TheoC  but which one though, ABC1 or ABC2?

sorry!

[TheoC]

@Pricey79 here is the full formula:

Yes Date = IF ( 'Table'[Yes or No Column] = "Yes" , 'Table'[Date Last Modified] , BLANK() )

[TheoC]

Hi @Pricey79 

How did you go with the below:

Date = IF ( AND ( 'ABC1'[Yes or No Column] = "Yes" ,'ABC2'[Yes or No Column] = "Yes" ) ,'Table'[Date] , BLANK() )

Cheers,

Theo