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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
avininpowerbi
Frequent Visitor

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

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

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

View solution in original post

4 REPLIES 4
v-cgao-msft
Community Support
Community Support

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

avininpowerbi
Frequent Visitor

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 !

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors