Using dynamic measures in CALCULATETABLE()

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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 )

[Anonymous]

Hey @Anonymous,

The DAX I provided shouldbe used to create a new table (Modelling -> New table), separate from your 'Date' table.

New Table button

Just checking this is that case?

Can you post the DAX syntax you've used to create your 'Date Range Start1' measure? 

Cheers,

Andy

[Anonymous]

Hi @Anonymous ,

I'm using Visual Studio to create a cube deployed to SSAS so on Power BI the Modelling-> New Table option is greyed out. In any case, yes, the Date Range table that I'm making using Visual Studio is separate and only referencing the static Date table and it's measures.

MEASURES (may be a little complicated, but the idea with [Date Range Start1] is that depending on the DateInterval selection, the DATEADD function returns the difference between the LASTDATE() and Period in terms of that DateInterval selection:

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)

                    ),

where the tables referenced are

'Period'[Period] = GENERATESERIES(0,365,1)

'Date Interval'[Date Interval]  = {("Day"),("Month"),("Quarter"),("Year")}

Kudos to you or anyone else if you can understand this from just looking at it or even trying it for yourself! The [Date Range Start1] measure should only return one value, depending on the Period and DateInterval selections.

[v-kelly-msft · Accepted solution]

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,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@v-kelly-msft works like a charm!

I'll mark yours as the solution, although if anyone is reading this, make note that the GENERATESERIES() function that @Anonymous suggested helped create he Date Range table too!

cheers.