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.
Hi,
I'd like to show a chart for the last 7 days and show 2 values for each day: current day and value one week ago. The source of the data is a star-modelled data warehouse with a fact table and a related date dimension.
Seems like a simple request, but I'm stuck.
Here are my measures:
Today = DATE(YEAR(NOW()); MONTH(NOW()); DAY(NOW())) DayDiff = SWITCH ( TRUE (); dim_Date[Today] < dim_Date[Date]; -1 * DATEDIFF (dim_Date[Today]; dim_Date[Date]; DAY); dim_Date[Today] > dim_Date[Date]; DATEDIFF (dim_Date[Date]; dim_Date[Today]; DAY); 0 ) Last7Days = IF(dim_Date[DayDiff]<7 && dim_Date[DayDiff]>=0; TRUE(); FALSE()) Impressions = SUM('fact_TweetEngagement'[ImpressionsDiff]) ImpressionsPW = CALCULATE( fact_TweetEngagement[Impressions]; DATEADD(dim_Date[Date]; -7; DAY) )
I added a bar-chart. To show only last 7 days I set the visual filter to Last7Days=TRUE. I added Impressions and ImpressionsPW measure. The chart shows Impressions measure, but not ImpressionsPW. It seems that the visual filter affects the ImpressionsPW measure so the measure is not returning any values.
Here's an example of a modified chart showing last 2 weeks:
As you can see the ImpressionsPW value is not returning values for dates older than 1 week, because it's out of the visual filter context (7+7...).
What am I doing wrong?
Hi sash,
I think the impressionSPW not showing the earlier week data should be caused the the DATEADD function.
By the way, could you please share some data sample so that we could build some texts based on that?
In addition, regarding showing the last 7 days, check the reply from smoupre:
Can I set page filters for "last 7 days"?
Regards
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 |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |