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.

Power BI if between two dates
Power BI if between two dates

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)))
Power BI if between two dates
Power BI if between two dates

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:

Power BI if date is blank
Power BI if a date is blank

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]
)
Power BI if date is blank
Power BI if a date is blank

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).

 power bi if date after today
power bi if date after today

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.

Show power bi if date after today
Show power bi if date after today

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", "***" )     
Power bi if date before today
Power bi if date 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.

Power BI if date is greater than another
Power BI if a date is greater than another
Column = IF('Table'[Date1] > 'Table'[Date2], "greater", "Smaller")
Power BI if date is greater than another
Power BI if a date is greater than another

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") 
Power BI if less than the another date
Power BI if less than the other date

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])
Power BI if date is in current month
Power BI if a date is in the current month

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:

check Power BI if date is in current month
check Power BI if a date is in the current month

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'))
Power BI if max date
Power BI if a max date

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.

Power Bi if year equals
Power Bi if year equals

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")
Power Bi if year equals to current year
Power Bi if year equals to the current year

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")
Power BI if statements with dates
Power BI if statements with dates

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.

power bi if date is blank then today
power bi if a date is blank then today

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])
Show Power BI if date is blank then today
Show Power BI if a date is blank then today

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:

power bi if and date
power bi if and date

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","***" ) 
Use Power BI IF with AND on date
Use Power BI IF with AND on date

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.

power bi last date of month
power bi last date of the month

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) 
Show Power BI last date of month
Show Power BI last date of the month

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:

Power BI IF date is less than today
Power BI IF date is less than today

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")
Power BI IF date is less than today
Power BI IF date is 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:

power bi last day of current month
power bi last day of the current month

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) 
power bi last date of previous month
power bi last date of the previous month

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
power bi last date of week
power bi last date of the Current week

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.

power bi last date of week
power bi 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 
Show Power BI last date of week
Show Power BI last date of the current week

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)
power bi last date of year
power bi last date of the current year

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)      
power bi last date of previous year
power bi last date of the previous year

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:

power bi check if date is in last month
power bi check if the date is in last month

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])
power bi check if date is in last month
power bi check if the date is in last month

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.

check Power BI if date is in last month
check Power BI if the date is in last month

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()))
power bi count if date before today
power bi count if date before 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:

Power BI SUM if before date
Power BI SUM if before date

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()) 
Power BI SUM if before date
Power BI SUM if before date

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))
calculate sum if before date in Power BI
calculate sum if before a specific date in Power BI

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.

power bi check if value is a date
power bi check if the value is a date
Valid or Not = if (try Date.ToText([Dates],"yyyy-mm-dd") otherwise "Invalid") = "Invalid" then "Invalid" else "Valid"
check if the value is date or not in Power BI
check if the value is a date or not in Power BI

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

check if value is a date in Power bi
check if the value is a date in Power bi

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:

power bi if compare date
Power BI if compare date

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())
Compare dates using Power BI
Compare dates using Power BI

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.

power bi check if date is weekend
power bi check if the date is weekend

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

Weekdays = FORMAT('Date Table'[Dates],"DDDD")
check if date is weekend power bi
check if the date is weekend power bi

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()) 
Power BI check if date is weekend or not
Power BI check if the date is the weekend or not

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:

power bi check if date is holiday
power bi check if the date is a holiday

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 )
)
check if date is holiday in Power BI
check if the date is a holiday in Power BI

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])
check if the date is holiday in Power BI
check if the date is a holiday in Power BI

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"
)
power bi check if date is holiday
power bi check if the date is a holiday

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:

Power BI what if parameter date
Power BI what-if parameter date

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
    )
)
Power BI Date what if parameter
Power BI Date what-if parameter

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

Power bi date what-if parameter
Power bi date what-if parameter

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

What if parameter on Power BI using date

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:

Date What-if parameter on Power BI
Date What-if parameter on Power BI

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:

Show Date what-IF parameter on Power BI
Show Date what-IF parameter on Power BI

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

Show Date what-IF parameter on Power BI
Show Date what-IF parameter on Power BI

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

You may like the following Power BI tutorials:

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?
  • Hi Bijay Kumar – Thank you for this content, super helpful! Do you know how could I limit in Power Query to show data just until last month? For example, imagine we are in August, we add data until Jul-31 and as well the 1st day of August, but I wanted to show only the last day of the previous month (i.e.: July-31?) Thank you!

  • >