Tag Archives: DAX

ByHariharan Rajendran

DAX Leap Year In Power BI

Identifying the Leap is very simple logic, this post explains that how you can identify a year which is a leap year or non-leap year in Power BI.

Consider I have a year column on my table. If not then use the Year function on the data column and get the year column.

You need to find the below values,

  1. Year Start Date
  2. Year End Date
  3. Days between Year Start Date and Year End Date.
  4. Check the number of days

That’s it, very simple.

Use the below DAX expressions to identify the above values.

YearStartDate = DATE(‘Year'[Year],01,01)

YearEndDate = DATE(‘Year'[Year],12,31)

TotalDays = (‘Year'[YearEndDate]-‘Year'[YearStartDate])*1+1

IsLeapyear = IF(‘Year'[TotalDays]=366,”Yes”,”No”)

Happy Learning.

ByHariharan Rajendran

DAX Query CALENDARAUTO()

In Power BI, you can use the pre-defined function CALENDARAUTO() to create date table with a column called “Date”. It will generate a date for current 1 year by default.

This above function will only work when you have a model with at least one column with date or datetime data type.

In the below screenshot, I tried to use CALENDARAUTO() function in an empty model.

You can see the warning message above.

“CALENDARAUTO() function can not find a base column of DateTime type in the model”

In the same scenario, I just added a simple table with one date data type column then I used the CALENDARAUTO() in a new table and this time I got the expected result.

Happy Learning. Share your comments.

1