In this post, I’ll walk you through a creative technique to omit writing the IF function and instead use the MIN and MAX functions in Excel or in Power BI. Let’s get started!
Optimizing IF using MAX and MIN Functions – Video
Consider the following Data
I have the following table with Product, Delivery Date, and Revised Date.
I am trying to calculate two additional columns
- Delayed Days = Revised Date – Delivery Date
- Early Arrival Days = Delivery Date – Revised Date
The Catch – A simple subtraction won’t work because Revised Date can either be later or earlier than the Delivery Date which might return negative values in either of the column calculations.
Calculating Delayed Days
Consider this DAX where I use IF to create the column
Delayed Days = IF( Data[Revised Date]> Data[Delivery Date], VALUE(Data[Revised Date] - Data[Delivery Date]) )
Which certainly produces correct results
But the same can be done without using IF by using MAX function
Delayed Days = MAX( VALUE ( Data[Revised Date] - Data[Delivery Date] ), BLANK () )
For every negative value MAX function limits the result as BLANK. (Since a blank or a 0 is > than a negative value)
Similarly, we will find the Early Days i.e Delivery before the Delivery Date.
Early Days =
MAX (
VALUE ( Data[Delivery Date] - Data[Revised Date] ),
BLANK ()
)
Correct outcome
More on Power BI
