Power BI if date + 27 Examples

In this Power BI Tutorial, we will discuss everything on Power BI if date with various examples.

• Power BI if between two dates
• Power BI if date is blank
• power bi if date after today
• Power BI if date is before today
• Power BI if date is greater than other date
• Power BI if date is in current month
• Power BI if less than the other date
• Power BI if max date
• Power Bi if year equals to current year
• Power BI if statements with dates
• power bi if date is blank then today
• power bi if and date
• power bi last date of month
• Power BI IF date is less than today
• power bi last day of current month
• Power BI last date of current week
• power bi last date of current year
• power bi last date of previous year
• power bi last date of previous month
• power bi check if date is in last month
• power bi count if date before today
• power bi sum if before date
• power bi check if value is a date
• Power BI IF Compare dates
• Power BI check if date is weekend or not
• power bi check if date is holiday
• Power BI what if parameter date

Power BI IF Dates between

In Power BI, a DATESBETWEEN() is a kind of time intelligence function that is used to return a table that contains a column of dates that begins with a specified start date and continues until the specified end date.

The syntax for this function is:

DATESBETWEEN(<Dates>, <StartDate>, <EndDate>)

Where <Dates> is a column that contains dates and <StartDate>, <EndDate> – A date expression.

Also, check Power BI IF + 31 Examples

Example: Power BI if between two dates

To implement this function, we are going to use this sample excel data. Now we will create a table using order date, start date, sales.

Then we will create a calculated column to show the sum of product sales in between some specific dates using this DATESBETWEEN function.

Total_sales_in_between_specific_date = CALCULATE(SUM(Orders[Sales]),DATESBETWEEN(Orders[Order Date], DATE(2015,1,1),DATE(2015,1,3)))

According to our expression, it is showing the total sales between the start date(i.e. 1/1/2015) to the end date(i.e. 1/3/2015).

This is how to use Power BI if between two dates.

Read Power BI Pie Chart

Power BI if date column is blank

Here we will see how to calculate the date column or how to show the date column if the date column is blank.

For this, here we have created a table having some columns and dates with blank data like below:

Now we will create a column that will show if the date1 is blank then it will show the date2’s date. If date2’s date is blank then it will show date3. Otherwise, it will show the date1’s date. The expression is:

Actual date = IF (
    'Table'[Date1] = BLANK (),
    IF ( 'Table'[Date2] = BLANK (), 'Table'[Date3], 'Table'[Date2] ),
    'Table'[Date1]
)

On the above table, we can see if there is no blank date then by default, it showing the date1’s date. This is how to show Power BI if a date is blank.

Read Power BI Switch – DAX function

Power BI IF a date after today

In this example, we will see how to distinguish a date if that will come after today. For this, here we have created a table having Project IDs, their Start Date, and End date(that contains some upcoming dates from today i.e. 4/10/2021).

Now we will create a column that will show whether the end date is a future date or not. If it is an upcoming date then it will return True otherwise, it will return false.

As there are 2 columns contain the upcoming dates, so it is showing True according to expression and the rest are showing as False.

Read Power BI Card How to use with Examples.

Power BI IF Date is before today

Similarly, we will see how to check whether the date is before today or not. For this again we will create another column and it will show if the date is before today then it will return as a text “before today” otherwise, it will return a false value (“***”).

Future = 
IF ( TODAY() > 'Table (2)'[EndDate], "before today", "***" )     

This is how to show if a date is before today or not in Power BI.

Read Power BI Measure Sum and Subtract Example

Power BI IF date is greater than another

Here we will see how to check if a date is greater than another date. For this, we have created a table having two date columns such as Date1, Date2 with some random dates.

Column = IF('Table'[Date1] > 'Table'[Date2], "greater", "Smaller")

This is how we can check whether a date is greater than another date or not in Power BI.

Read Remove blank from Power bi slicer

Power BI if less than the another date

Similarly, we can compare a date to whether it is less than another date or not. For this, here also we will create another calculated column that will check if the date is less than another date then it will return “Less than”; otherwise, it will return “Not less than”.

Column = IF('Table'[Date1] < 'Table'[Date2], "Less than", "Not Less than") 

This is how to check if a date is less than the other date in Power BI.

Read Difference between USERNAME() and USERPRINCIPALNAME() in Power BI Dax

Power BI if date is in current month

Here we will check how to whether the given date is in the current month or not. To execute this example, we are going to use the previous date table.

There is a date function available in Power BI named “Date.IsInCurrentMonth()” that checks the given date occurs in the current month or not.

We can use this function in the Power Query. Using this function we will add a column in the Power query. To add a column, go to transform data on Power BI, it will open the Power query editor.

Then go to add column tab > custom column. Here we can insert the below formula:

IsItInCurrentMonth = Date.IsInCurrentMonth([Date2])

After clicking on OK, we can see the result as True or False. It returns True if the given date occurs in the Current month. Otherwise, it returns False like below:

It is showing the result according to Date2’s date data. This is how we can easily check Power BI if a date is in the current month.

Read How to create and use Power BI Bookmarks

Power BI IF max date

Now we will see how to get the max date from a date dataset. To implement this, we are going to use the previous date table and under that table, we will create a measure that will show the max date from the total selected dataset.

Max Date = CALCULATE(MAX('Table'[Date2]),ALL('Table'))

Here we are using a card visual to show the max date. This is how we can show the max date from a large Date dataset on Power BI.

Read difference between calculated column and measure in Power BI

Power Bi if year equals to current year

Here we will check whether the selected date occurs in the current year or not. Let’s create a dataset having some random dates including dates from the current year and the previous year.

To check whether the date is in the current year or not, we will create a column under that table:

is_it_current_year = IF(YEAR(ProjectTable[Delivered Date]) = YEAR(Today()),"Yes", "No")

It is showing yes if the date’s year matches the current year; otherwise, it shows no. This is how to check Power Bi if year equals to the current year.

Read Power bi free vs pro vs premium

Power BI IF statements with dates

Here we will see how to use the date column in a conditional statement in Power BI. For this, we are going to create a calculated column that shows if the date is greater than or equal to the current date then it returns a true value (i.e. 1); otherwise, it will return a false value(i.e. 0).

If_with_dates = IF('Table'[Date2] >= TODAY(), "1", "0")

As there are only 2 dates those are greater than or equal to today’s date. So it returns the result as 1 and the rest are showing 0. In this way, we can use Power BI if statements with dates.

Read How to use weekday function power bi with example

Power BI IF date is blank then today

Now we will see, how to show today as default if the date is blank. To show this, here we have created a table having columns such as Project IDs, startDate, and End Date with some blank data.

Now we will create a column that will check if the date data is blank then instead of blank, it will show today’s date.

Column 2 = IF('Table (2)'[EndDate] = BLANK(), TODAY(),'Table (2)'[EndDate])

This is how to Show Power BI if a date is blank then today.

Read Contact your admin to enable embed code creation Power bi

Power BI IF and date

In this example, we will see how to use the date column in a conditional statement with AND operator.

For this, here we are going to use the date table that we have created previously. The table is:

Now we will create a column that will check if the Date1 is less than Date2 and Date2 is equal to today’s date(i.e. 5/10/2021), then it will show “Present”; otherwise, it will return as the symbol(for example we will show this as *** symbol).

Progress = IF( 'Table'[Date1] < 'Table'[Date2] && 'Table'[Date2] = TODAY(), "Present","***" ) 

It is showing “Present”, only where if both conditions are true. This is how we can Use Power BI IF with AND on the date.

Read Power BI convert hours to minutes

Power BI last date of the month

In this example, we will see how to show the last dates of each month in a date dataset. For example, here we have a table having some dates.

Now the question is how to show the last date of the month for each date. For this, there is a DAX function in Power BI named “EOMONTH()” under the Date and time function that returns the date in DateTime format of the last date of the month, before or after a specified number of months.

Under this table, we will create a calculated column that will show the last date of the month.

last date of the Month = EOMONTH('Table'[Date2],0) 

This is how to show Show the last date of the month in Power BI.

Read Power BI convert yyyymmdd to date

Power BI IF date is less than today

In this example, we will see how to check if a date is less than today or not. For this, here we have created a data table having some dates like below:

Now we will create a calculated column that will check if the date is less than today then it will return a true value; otherwise it will return a false value:

less_than_today = IF('Table'[Date2] < TODAY(), "less than today", "not less than today")

It returns the result as a text according to true and false values in our expression. This is how we can show Power BI IF the date is less than today.

Read Get Month Name from Month Number in Power BI

Power BI last day of current month

Here we will see how to show the last day of the current month in Power BI. For this, we will simply create a calculated column and insert the below code. It will show the last day of the current month as well as dynamically changed over time.

last day of current month = EOMONTH(TODAY(),0)

Now we will use a card visual to show this expression:

This is how we can show the last day of the current month in Power BI.

Read How to use Power BI sync slicers

Power BI last date of previous month

Similarly, we will see how to display the date of the previous month in Power BI. For this, we will create a calculated column:

last date of the previous Month = EOMONTH(TODAY(),-1) 

As the current month is “October”, so it is showing the last date of “September“. This is how to show the last date of the previous month

Read Power BI Calculated Column Example

Power BI last date of current week

Now we will see how to display the last day of the current week dynamically. Here we will display the last date of the current week with examples.

Example-1: For working days(Monday – Friday)

Usually, we calculate the working days from Monday to Friday. To show the last date of the current week, we will create a measure:

EndOfCurrentWeek = 
TODAY () - WEEKDAY ( TODAY (), 2 ) + 5

On the above screenshot, one card is showing the current date(for our reference) and the other one is showing us the last date of the current week.

Example-2: Calculate the last date of the current week

Here, it will calculate and show the last date of the current week i.e. from Monday to Saturday. For this, we are going to another measure like below:

EndOfCurrentWeek = 
TODAY () - WEEKDAY ( TODAY (), 2 ) + 6 

This is how to show the last date of the current week in Power BI.

Read Power bi conditional column example

Power BI last date of the current year

Here we will see how to display the last date of the current year. For this here we are going to create a calculated column that will show the last date of the current year and also will change dynamically according to year change.

Last date of the current year = DATE(YEAR(TODAY()),12,31)

In this simple way, we can display the last date of the current year in Power BI.

Power BI last date of the previous year

Similarly, we will see here how to display the last date of the previous year(according to the current year).

Last date of the previous year = DATE(YEAR(TODAY())-1,12,31)      

In this way, we can display the last date of the previous year in Power BI.

Read How to split column in Power bi

Power BI check if date is in last month

Here we will check, whether the date is in last month or not in Power BI. For this, there is a function in the date function named “Date.IsInPreviousMonth()” under the Power Query M functions. That indicates whether the given DateTime occurs during the previous month, as determined by the current date and time on the system.

To implement this, we will create a data table having some random dates:

Now we will add a custom column on this table, to show whether the mention dates occur in the last month or not.

For this, go to Transform data > Add column tab > custom column. Then insert the below formula on the custom column.

IsItInCurrentMonth = Date.IsInPreviousMonth([Dates])

After clicking on ok we can see the Date table has added one more column to show that the dates occur in the previous month or not. It will show True, if it occurs in the previous month otherwise, it will show False.

This is how to check Power BI IF date is in last month.

Read How to change data source in Power Bi

Power BI count if date before today

Let’s take another example to count the total dates if there are occur before today. For this, we are going to use the previous date table that we have created.

On that table, we will create a measure that will calculate the total dates before today:

Dates_Before_Toady = COUNTROWS(FILTER('Date Table','Date Table'[Dates]<TODAY()))

As the current date is 10/6/2021(mm/dd/yyyy), so it counts the dates before today. This is how to count if date before today in Power BI.

Power BI sum if before date

Here we will see how to calculate the sum if that occurs before a specific date or today. To implement this, we have created a table like below:

For example, here we will create a measure that will calculate the total sales before today:

Sales_before_today = CALCULATE( SUM('Date Table'[Sales]), 'Date Table'[Dates] < TODAY()) 

We used a card visual to show the total sales before today. Instead of today, we can specify any dates from the table. For example, we want to show the total sales before 9/15/2021. For this, the measure will be:

Sales_before_specific_date = CALCULATE( SUM('Date Table'[Sales]), 'Date Table'[Dates] < DATE(2021,9,15))

This is how we can calculate the sum if the before a specific date in Power BI.

Read Power BI Quick Insights

Power BI check if value is a date

Here we will see how to check whether the inserted value is a date or not. For example, here we have created a table with some random dates(including invalid dates).

Now we will add a custom column under this table, to check whether the value is a valid date or not.

Valid or Not = if (try Date.ToText([Dates],"yyyy-mm-dd") otherwise "Invalid") = "Invalid" then "Invalid" else "Valid"

After clicking on OK, we can a new column got added to that table having data if it is a valid date not.

It is showing invalid if the value is not a valid date. This is how to do check if the value is a date or not in Power BI.

Read Power BI Group By Examples

Power BI if compare date

Now we will see how to compare a date with another date. For example, here we will use a table that we have created previously. That table has two date columns with some random dates such as:

Now we will add a calculated column that will compare if Date2 is greater than or equal to Date1, then it will return Date2; unless, it will return a blank value.

Compare_Dates = IF([Date2]>=[Date1],[Date2],BLANK())

It returns the result according to a comparison between two dates. This is how to do Compare dates using Power BI.

Read Power bi shared dataset permissions management

Power BI check if date is weekend

As we know from the 7 days, Saturday and Sunday are known as the weekend. In this, we will see how to check whether the date is a weekend or not.

For example, here we are going to use the date table having some dates, that we have created previously.

On that table, first, we will add a calculated column that will format the dates into weekdays.

Weekdays = FORMAT('Date Table'[Dates],"DDDD")

Again we will create another calculated column that will check whether the day is a weekend or not:

Check_Weekend = IF( 'Date Table'[Weekdays] = "Saturday" || 'Date Table'[Weekdays]="Sunday", "Weekend",Blank()) 

Now we can see the date got distinguished whether it is a weekend or not. This is how Power BI check if the date is a weekend or not.

Read Scheduled Power BI report data refresh

Power BI check if date is holiday

Here we will check whether the date is a holiday or not in Power BI. For example, we have created a holiday list table having the holiday’s dates and names like below:

Now we will create another calendar table that contains dates with weekdays. For example, we are going to create a calendar, contain dates from October to December 2021.

Calendar = 
ADDCOLUMNS(CALENDAR(DATE(2021,10,1),DATE(2021,12,31)),
"WeekDay", WEEKDAY ( [Date], 2 )
)

Under this calendar table, we will add another column using LOOKUPVALUE() to create the holiday list on the calendar table:

Holiday Column = LOOKUPVALUE('Holiday Table'[Holiday's Name],'Holiday Table'[Date],'Calendar'[Date])

It is only showing the holiday’s name. But we want to show whether the date is a holiday or working day or weekend. For this, again we will create another column under the calendar table:

working day = 
SWITCH (
TRUE (),
ISBLANK ( 'Calendar'[Holiday Column] )
&& 'Calendar'[WeekDay] < 6, "working day",
ISBLANK ( 'Calendar'[Holiday Column] )
&& 'Calendar'[WeekDay] > 5, "weekend",
"holidays"
)

This is how to check if the date is a holiday or not.

Read Power BI nan error (Not a number) while dividing by Zero

Power BI what if parameter date

In Power BI, there are two types of parameters i.e. Power Query parameter and What-IF parameter. A Power query parameter is used to creating a dynamic structure for the data transformation page but the What-IF parameter is used to make changes for users and see that changes immediately on the report.

To implement this, here we are going to use our sample data based on products ordered. Here we will create a table, having these columns:

For example, here we will create a measure that will calculate the total sale of last month. Using this measure, we can calculate the total sales for 2 months, 6 months, etc. through a slicer.

Sales For Last Month = 
CALCULATE(
    SUM(Orders[Sales]),
    PARALLELPERIOD(
        Orders[Order Date].[Date],
        -1,
        MONTH
    )
)

On the above table, we navigate to one month back by using -1 on the second parameter of the parallel period function. If we use -2 instead of -1 then it will be got parallel to two months back.

Now we will create a What-if parameter on that. For this, go to modeling tab > new parameter

After clicking on OK, we can see a slicer on the Power BI report page having values from 1 to 12.

We can see under the month value, another measure is created. That will show us the value, selected on the measure in the range of 1-12. We will use a card visual to show this measure:

Finally, we have created a parameter. Now we will create a measure that will calculate the total sales of N month ago:

Total sales N Months ago = 
CALCULATE(
    SUM(Orders[Sales]),
    PARALLELPERIOD(
        Orders[Order Date].[Date],
        -1*[month back Value],
        MONTH
    )
)

Here, we multiply -1 with parameter value as it is a positive value but for calculating the few months back so we need a negative value. And now we will use this measure on that table:

For example, when we slicing the value from 1 to 4 or 5, it will display the value as parallel in parameter like below:

This is how to Show the Date what-IF parameter on Power BI.

You may like the following Power BI tutorials:

• Power BI report using People Picker Field
• Data Labels in Power BI
• Power bi table visualization
• Subtraction in Power bi using DAX
• DAX Filter function (Text column) in Power BI
• Power BI integration with PowerApps Portals

Conclusion

In this Power BI Tutorial, we discussed all the Power BI IF date. Also, we discussed:

• How to use Power BI IF between two dates?
• How to show if a date is blank on Power BI?
• How to show Power BI if date after today?
• How to show Power BI if date before today?
• How to check if date is greater than other in Power BI?
• How to check if date is less than other in Power BI?
• How to check whether the date is in current monthor not in Power BI?
• How to show Power BI if max date?
• How to check Power Bi if year equals to current year?
• How to use Power BI if statements with dates?
• How to show today’s date if date is blank in Power BI?
• How to use date in Power BI if with AND operator?
• How to show last date of month in Power BI?
• How to show Power BI IF date is less than today?
• How to display Power BI last day of the current month?
• How to dispaly last day of the previous year in Power BI?
• How to display Power BI last day of the current week?
• How to display Power BI last date of the current year?
• How to display the last date of the previous year in Power BI?
• How to show Power BI check if date is in last month?
• How to check if a value is a date or not in Power BI?
• How to check Power BI check if date is weekend or not?
• How to check if date is holiday in Power BI?
• How to do Date what-IF parameter on Power BI?