Date Table cannot calculate a date outside of the table range

[Aree]

I recent encountered a problem with a date table that was generated with the Calendar function.

If i attempt to calculate a date outside of the tables range I encounter a blank value.

Scenario:

I calculate a date table between 1/Jan/2018 to 31/Dec/2018. When using these dates against financial data the requirement is often to show the Financial Year and Not the Calendar year. In my scenario the company's financial year spans from July to Jun. So the date range span across Financial Year 2017/2018 to 2018/2019. To perform my Financial Year calculation i would determine the financial year by taking a given date and add 6 months then extracting the year YEAR(DATEADD(<date>, 6, MONTH)). Any date after 01/Jul/2018 will return a blank result since the DATEADD function calculates a date outside of the tables date range.

Now i am using an IF statement to correct this by shifthing to YEAR(DATEADD(<date>, -6, MONTHS))+1 when a blank is returned but, it would be nice if this issue can be looked at.

Thank you and i appologise for the long winded explaination 🙂

[v-yuezhe-msft]

@Aree,

This is how DATEADD function works, as per this online article, the result table includes only dates that exist in the dates column. 

When you directly add a column in the date table using DAX below,  you will find that the new column also returns blank values after 01/jul/2018.

Column = DATEADD('Date'[Date],6,MONTH)

In your scenario, ensure that the date table you create covers the latest dates you want to display. This way, you will not get blank values in your fact table.

There is a similar thread for your reference.
https://community.powerbi.com/t5/Desktop/DateAdd-returns-blank-values/m-p/58850



Regards,
Lydia

[Anonymous]

Just because it's documented doesn't mean this isn't stupid. Can we get a real solution for this?

If I can do this

DATE(YEAR(<date>); MONTH(<date>)+1; DAY(<date>))

Why can't I do the same just with DATEADD?