Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
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.
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |