cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
asfgasfasf
Frequent Visitor

Line graph with previous period rather than previous 7 days

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:

asfgasfasf_0-1632145978174.png

 

My graph looks like this:

asfgasfasf_1-1632146019416.png

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:

asfgasfasf_3-1632146257824.png

 

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:

asfgasfasf_4-1632146393383.png

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

 

Any help would be appreciated. Thanks. 

 

 

 

 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @asfgasfasf ,

 

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.

16.png

 

 

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.

View solution in original post

2 REPLIES 2
v-stephen-msft
Community Support
Community Support

Hi @asfgasfasf ,

 

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.

16.png

 

 

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.

View solution in original post

MFelix
Super User
Super User

Hi @asfgasfasf ,

 

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.

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.