Dynamic Date Ranges in a Chart

DAX DAX Calculations
1

Hi all,

Please see attached example pbix: Chart with Dynamic Dates v1.pbix (131.7 KB)

I am wanting to create a line chart to show our stock trend over a time, over a period as selected by the user, eg: 1W, 2W, 1M, 3M, 6M, YTD and 1Y.

I have achieved this by using a disconnected table ‘Time Frames’, which contains Start and End dates for each option:

image

I have then got a measure Units Dynamic which returns the number of units, based on the selected date range:

Units Dynamic = 
IF (
    HASONEVALUE ( 'Time Frames'[TimeFrame] ),
    CALCULATE (
        [Units],
        FILTER (
            Dates,
            Dates[Date] >= MAX ( 'Time Frames'[StartDate] )
                && Dates[Date] <= MAX ( 'Time Frames'[EndDate] )
        )
    ),
    [Units]
)

This works well so far:

image
image962×603 18.1 KB

However I now want to add a line for Prior Year. I have tried creating a 2nd measure Units Dynamic PY 1 as below, however this doesn’t work.

Units Dynamic PY 1 = 
CALCULATE([Units Dynamic], 
    SAMEPERIODLASTYEAR(Dates[Date])
)

I have also tried creating a 2nd set of columns in my Time Frames table, to return the Start and End dates less 1 Year and use the same calculation as Units Dynamic, but referring to these PY columns, however this just ends up with me having two different date ranges, eg Jan -June 2019 and Jan-June 2020:

image
image939×615 25.2 KB

The goal is to have the Prior Year line overlaying Units Dynamic, as is usually achieved using SAMEPERIODLASTYEAR.

Any help very much appreciated!

Mark

2 Likes
2

@Mark,

Check out this thread from @JarrettM. I think his approach will get you everything you need here.

  • Brian
3

Hi @Mark,

I have to say, it’s a very nice concept to present data! Well done! :+1:
I think I have managed to achieved the desired result just slightly changing one measure.
Why don’t you try this out?

Units Dynamic PY 1 =
IF(
    HASONEVALUE( 'Time Frames'[TimeFrame] ),
    CALCULATE(
       [Units PY],
        FILTER(
            Dates,
            Dates[Date] >= MAX( 'Time Frames'[StartDate] ) &&
            Dates[Date] <= MAX( 'Time Frames'[EndDate] )
        )
    ),
   [Units PY]
)

Units PY =
CALCULATE( [Units], SAMEPERIODLASTYEAR( Dates[Date] ) )

image
image799×502 56.3 KB

image

image

Chart with Dynamic Dates v2.pbix (121.8 KB)

Take care,
Mariusz

2 Likes
4

Hi @mno ,

Awesome, I don’'t know why I didn’t try that! I knew it would be something simple!

In fact your measure can be simplified slightly by simply referencing Units PY and without having to use the 2nd CALCULATE:

Units Dynamic PY 3 =
IF (
    HASONEVALUE ( 'Time Frames'[TimeFrame] ),
    CALCULATE (
        [Units PY],
        FILTER (
            Dates,
            Dates[Date] >= MAX ( 'Time Frames'[StartDate] )
                && Dates[Date] <= MAX ( 'Time Frames'[EndDate] )
        )
    ),
    [Units PY]
)

I hope you are keeping well and hopefully we’ll be back at the London user group sometime soon (if you are the same Mariusz that is :laughing:)

@BrianJ - Thanks also for taking a look and suggesting the M code solution, however it was the Prior Year line which was my issue. I also always try to avoid any Bi-Directional filters, as in the words of the great Alberto Ferrari “You can use Bi-Di, but you will go to hell” :grin:

Cheers

Mark

5

@Mark,

I’m right there with you, brother. I feel like I do everything short of passing this article out to trick-or-treaters to discourage the use of bidirectional relationships.

HOWEVER… this is the one exception where I’ve come to the conclusion that they are okay to incorporate for this very limited purpose as a snowflake off of the dimension table. I take Russo and Ferrari as gospel, but in this case Chris Webb gives it the stamp of approval, and the entire eDNA expert team (except for me) used this construct on their entries for Data Challenge #5.

Not encouraging you to use it here, since you’ve got another good solution, but also not to discard it out of hand just because it uses the accursed bidirectional relationship.

  • Brian
2 Likes