Forum Discussion
Using dynamic measures in CALCULATETABLE()
Hi again,
I previously created a topic regarding this and it was sort of all over the place in terms of how I laid it out. I will summarize it here instead.
I want to create a 'Date Range' table that only displays all dates from a static 'Date' table between a date as a measure onwards to LASTDATE(). My Measure on the static 'Date' table displays the date as a result of parameters inputs. The measure is called [Date Range Start1] and, as it stands, returns a date in the DATETIME format depending on parameters that can be changed by sliders.
Here is the DAX that I am trying to use to create the dynamic table:
Date Range
=
VAR datethreshold = ( 'Date'[Date Range Start1] )
RETURN
CALCULATETABLE (
'Date',
FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] >= datethreshold )
)
The table on Visual Studio only returns the LASTDATE (which in this case, is 31/01/2020) but when loading it into Power BI and trying to change the datethreshold measure, only one value is displayed and all dates inbetween the threshold and the LASTDATE() are missing. Below is what I am seeing:
So the Period slider starts at 22 and the DateInterval option is set to Day. Therefore, the [Date Range Start1] measure returns the value of 22 days previous to the LASTDATE() of the static date table (which is 31/01/2020). What I want instead is for the table to display ALL dates between the measure [Date Range Start1] and LASTDATE() (in this case, it would be 09/01/2020, 10/01/2020, 11/01/2020 ... 30/01/2020, 31/01/2020)
I read somewhere that measures are executed at query level and calculated tables are executed at refresh level. Does that affect this at all?
I hope that this is all the information that you need to understand the issue. If there is any more information that is required
Hi Anonymous ，
Drag the measure to the visual level filter:
Date Range Start1:= SWITCH( TRUE(), FIRSTNONBLANK('Date Interval'[Date Interval], TRUE()) = "Day", FIRSTDATE( DATEADD(LASTDATE('Date'[Date]), -MIN('Period'[Period]), DAY) ), FIRSTNONBLANK('Date Interval'[Date Interval], TRUE()) = "Month", FIRSTDATE( DATEADD(LASTDATE('Date'[Date]), -MIN('Period'[Period]), MONTH) ), FIRSTNONBLANK('Date Interval'[Date Interval], TRUE()) = "Quarter", FIRSTDATE( DATEADD(LASTDATE('Date'[Date]), -MIN('Period'[Period]), QUARTER) ), FIRSTNONBLANK('Date Interval'[Date Interval], TRUE()) = "Year", FIRSTDATE( DATEADD(LASTDATE('Date'[Date]), -MIN('Period'[Period]), YEAR) ),Set the period you need.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
6 Replies
Hi Anonymous,
Have you tried using the GENERATESERIES() function?
Assuming you've already figured out how to dynamically calculate your Start & End Dates, something like this should do the trick if you replace the 3 variables with the dates/increment you've already calculated:
Table =
VAR StartDate = DATE(2020, 01, 09)
VAR EndDate = DATE(2020, 01, 31)
VAR Increment = 1
RETURN
GENERATESERIES(StartDate, EndDate, Increment)Your output table will then look something like this:
Cheers,
Andy
Hi,
I was excited to try this solution Anonymous but it seems that the DAX I've implemented (below) only returns the screenshot below that. I'm thinking that either VAR cannot be dynamic or (more likely) that I have implemented my measures incorrectly.
Date Range =
VAR StartDate = ( 'Date'[Date Range Start1] )
VAR EndDate =
LASTDATE ( 'Date'[Date] )
VAR Increment = 1
RETURN
GENERATESERIES ( StartDate, EndDate, Increment )