Forum Discussion

ashely_nave's avatar
ashely_nave
New Member
2 years ago
Solved

If statement with date

I have linked to our new maintenance system in PowerBi. I am trying to automate the machine downtime metric. In the calculation date field, I have an IF statement that says 

Calculation Date = if(isblank([Completed on]),[Current Date],[Completed on].[Date]). The point of this one is that to measure the downtime to date, I have to have an end. So if the work order in not completed, it returns the current date. This one works well.
I am new to PowerBi so I am stumbling through this. 

 I have a field [created.on]. I need a statement that says if [created.on] is before the current month, return the first date of the current month, if not return [created.on]. The reason for this is that downtime is measured by a monthly metric. So as the work order was started on 11/26, the downtime from 11/26-11/30 would be counted on the November downtime report. 

 

 

Please let me know if anything is not clear. Any help would be greatly appreciated. 

 

  • vicky_'s avatar
    vicky_
    2 years ago

    Please try the following formula:

    Start Date = 
var startOfThisMonth = EOMONTH(TODAY(), -1) + 1
return IF('Table'[created_at] < startOfThisMonth, startOfThisMonth, 'Table'[created_at])

2 Replies

  • vicky_'s avatar
    vicky_
    Super User
    2 years ago

    Please try the following formula:

    Start Date = 
var startOfThisMonth = EOMONTH(TODAY(), -1) + 1
return IF('Table'[created_at] < startOfThisMonth, startOfThisMonth, 'Table'[created_at])