Forum Discussion

avininpowerbi's avatar
avininpowerbi
Frequent Visitor
3 years ago
Solved

Date difference between today's date and a past date - issues with M language

Hi there ! 

 

I dont know where I am going wrong , Plz helpppp!  I am trying to get the following M code exceute , it is going ok for the first condition, but not working for the 2nd condition where Stage=To-Do. My " Due date" is from the system and I have selected only type date format. so now, I am wondering why the hell is throwing an Error in the 2nd conditon case .  

 

if [Stage]="Done" and [Estimated Time]>0 and [Recorded Time]>0 then [Estimated Time]-[Recorded Time] else if [Stage]="To-Do" and [Due]< DateTime.Date(DateTime.LocalNow() ) then DateTime.Date((DateTime.LocalNow() ) - [Due] ) else "null"

 

My main goal is to get the difference in hours from today's date mius Due date , but I am not getting days difference for now. 

 

I also tried

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi avininpowerbi ,

    Please try:

    if [Stage] = "Done" and [Estimated Time] > 0 and [Recorded Time] > 0 
then [Estimated Time] - [Recorded Time] 
else if [Stage] = "To-Do" and [Due] < DateTime.Date(DateTime.LocalNow()) 
then Duration.TotalDays(DateTime.LocalNow() - DateTime.From([Due])) 
else null

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

4 Replies

  • avininpowerbi's avatar
    avininpowerbi
    Frequent Visitor
    3 years ago

    and this is the error I get when I check : 

    Operator=-
    Left=5/25/2023 1:39:33 PM
    Right=2/16/2023

     

    you can see today's date is being shown as datetime format , and not the date format !

    • avininpowerbi's avatar
      avininpowerbi
      Frequent Visitor
      3 years ago

      I used a slight varation Date.From like below : 

       

      if [Stage]="Done" and [Estimated Time]>0 and [Recorded Time]>0 then [Estimated Time]-[Recorded Time] else if [Stage]="To-Do" and [Due]< Date.From(DateTime.LocalNow() ) then Date.From((DateTime.LocalNow() ) - [Due] ) else "null" 

       

      but its hsoing me the same error as Operator=-
      Left=5/25/2023 1:39:33 PM
      Right=2/16/2023

      • avininpowerbi's avatar
        avininpowerbi
        Frequent Visitor
        3 years ago

        and here is another one I tried ( Date.from)

         

        if [Stage]="Done" and [Estimated Time]>0 and [Recorded Time]>0 then [Estimated Time]-[Recorded Time] else if [Stage]="To-Do" and [Due]< Date.From(DateTime.LocalNow() ) then Date.From((DateTime.LocalNow() ) - Date.From([Due]) ) else "null"

         

        giving me this error : Operator=-
        Left=5/25/2023 2:07:26 PM
        Right=2/16/2023

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi avininpowerbi ,

    Please try:

    if [Stage] = "Done" and [Estimated Time] > 0 and [Recorded Time] > 0 
then [Estimated Time] - [Recorded Time] 
else if [Stage] = "To-Do" and [Due] < DateTime.Date(DateTime.LocalNow()) 
then Duration.TotalDays(DateTime.LocalNow() - DateTime.From([Due])) 
else null

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum