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.
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!
Solved! Go to Solution.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you!! This worked very well!!
Covering 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 |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |