Line graph with previous period rather than previous 7 days

[Anonymous]

I have a line graph that currently compares current 7 days to previous 7 days.

The current 7 days is from my table / database

The previous 7 days line is being calculated with this measure:

My graph looks like this:

e.g. on the 19th it shows data for the 19th and also the data for the 12th

I want this graph to be more dynamic. To update the previous period depending on the days in the current period.

This is the new previous period measure I am trying to use:

I am using the dates 13/09/2021 to 19/09/2021. This gives a date difference of -6 days. Subtract 1 more so the date periods do not overlap. My new graph looks like this:

The previous period value now shows the value from the previous day rather than 7 days ago.

Any help would be appreciated. Thanks. 

[MFelix · Accepted solution]

Hi @Anonymous ,

This is happening due to context, I assume that the AA Calendar[Date] is the column you are using for the X-axis, so when you calculate the MAX and MIN value of that column you get the value for each of the x-axis values so you are always doing the following calculation:

DateDiff (Date - Date) -1 = (18/09 - 18/09)  - 1= 0 - 1 = -1

So for your calculation you are always getting -1 so the previous day you need to change your first variables to an aggregator based on the selected values of dates try the following two variables:

var max_date = MAXX(ALLSELECTED('AA Calendar'[Date]),'AA Calendar'[Date])

var min_date = MINX(ALLSELECTED('AA Calendar'[Date]),'AA Calendar'[Date])

This sould return expected result.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Create the following measure

Previous 7 days =
CALCULATE (
    SUM ( 'Table'[Visits] ),
    FILTER (
        'Table',
        [Date]
            IN DATESBETWEEN ( 'AA Calendar'[Date], TODAY () - 7, TODAY () )
    )
)

The measure is automatically updated based on the current date because of the TODAY() function.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix · Accepted solution]

Hi @Anonymous ,

This is happening due to context, I assume that the AA Calendar[Date] is the column you are using for the X-axis, so when you calculate the MAX and MIN value of that column you get the value for each of the x-axis values so you are always doing the following calculation:

DateDiff (Date - Date) -1 = (18/09 - 18/09)  - 1= 0 - 1 = -1

So for your calculation you are always getting -1 so the previous day you need to change your first variables to an aggregator based on the selected values of dates try the following two variables:

var max_date = MAXX(ALLSELECTED('AA Calendar'[Date]),'AA Calendar'[Date])

var min_date = MINX(ALLSELECTED('AA Calendar'[Date]),'AA Calendar'[Date])

This sould return expected result.