Calendar/GenerateSeries DAX functions giving error “The start date or end date in Calendar function can not be Blank value.”

Question

I am trying to create a separate table that will have dates between Start_Date and End_date. These dates are measures. Below is the detail of my requirement..

I have a Dropdown in report which lists distinct dates of fact.sales table and is a single select. I am saving this selected date in a Measure --> End_Date = selectedvalue(sale[Delivery Date Key]).

Now I am showing a report using what-if parameter. The slider slides from values 1 to 24.

Now I am creating another Measure..

Start_Date  = date(year('Date'[End Date]),MONTH('Date'[End Date])-Cust_Key[Cust_Key Value],1)`

The Problem - When I try to use calendar function to create table using below DAX function, I get an error - "The start date or end date in Calendar function can not be Blank value."

calendar = calendar('Date'[Start Date],'Date'[End Date])

Can measures not be used inside the Calendar/GenerateSeries function.. Thanks in advance..

Answer 1

The SELECTEDVALUE function will return a blank if you haven't selected a single value. Note that calculated tables cannot read slicer values since they are only computed when the data is loaded, not whenever you interact with a slicer. Therefore, your End_Date measure returns a blank since it isn't filtered by the slicer.

You can use measures within any calculated tables but they won't interact with any dynamic slicers or filters. Calculated tables in your data model are static once the model is loaded. You can, however, use temporary/dynamic calculated tables within a measure so long as the measure still returns a single value at the end.