Forum Discussion
Time comparison in line chart
Hello,
I've read many posts in this forum but did not find one answering my question.
I have a data model with a DimDate table (days from 01.2018 to today) and another table with Traffic (Google Analytics users) data, on a day basis. Both tables are connected in the data model (via the date dimension).
I have a slicer to allow report users to filter on dates.
I created a measure to calculate the traffic of the previous period. I don't use timeintelligence functions because I want the users to be able to select a custom date range (e.g 04/30 to 06/08 as shown above).
Var_Users =
VAR date_range = COUNTA(DimDate[Date])
VAR sum_prevperiod = IF(
date_range=0,0,
CALCULATE(SUM('Raw_Data'[Users]),dateadd(DimDate[Date],-date_range+1,DAY))
)
return sum_prevperiod
This works perfectly fine for "card" or "KPI" visuals for instance: the data displayed is the one from the previous period.
But I'd like to have a line chart comparing the data between the selected period and the previous period.
The issue is: The visual merges both lines into one, showing the same data.
Do you guys have any idea on how I can overcome this?
Thanks!
Hi Anonymous ,
This has to do with the context you have for your measure. When you are makking the daily chart and make the COUNTA(DimDate) you are only picking the current date on the visualization so your value is 1 then on your formula you do -1 +1 this is 0 so your result will be exactly the same as the current day.
For this to work you need to pickup the MAX and MIN date of the selected dates to get the number of days to be consider on your calcultions
Try the following measure:
Var_Users = VAR Max_date = MAXX ( ALLSELECTED ( dimDate[Date] ), dimDate[Date] ) VAR Min_date = MINX ( ALLSELECTED ( dimDate[Date] ), dimDate[Date] ) VAR date_range = DATEDIFF ( Min_date, Max_date, DAY ) VAR sum_prevperiod = CALCULATE ( SUM ( 'RawData'[Users] ), DATEADD ( DimDate[Date], - date_range + 1, DAY ) ) RETURN sum_prevperiod
2 Replies
- MFelix
Super User
Hi Anonymous ,
This has to do with the context you have for your measure. When you are makking the daily chart and make the COUNTA(DimDate) you are only picking the current date on the visualization so your value is 1 then on your formula you do -1 +1 this is 0 so your result will be exactly the same as the current day.
For this to work you need to pickup the MAX and MIN date of the selected dates to get the number of days to be consider on your calcultions
Try the following measure:
Var_Users = VAR Max_date = MAXX ( ALLSELECTED ( dimDate[Date] ), dimDate[Date] ) VAR Min_date = MINX ( ALLSELECTED ( dimDate[Date] ), dimDate[Date] ) VAR date_range = DATEDIFF ( Min_date, Max_date, DAY ) VAR sum_prevperiod = CALCULATE ( SUM ( 'RawData'[Users] ), DATEADD ( DimDate[Date], - date_range + 1, DAY ) ) RETURN sum_prevperiod