Forum Discussion

Pricey79's avatar
Pricey79
Helper V
4 years ago

Date change query

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

Need Help

16 Replies

  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

    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's avatar
      Pricey79
      Helper V
      4 years ago

      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's avatar
        TheoC
        Community Champion
        4 years ago

        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

         

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.