Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I am a new user in PBI, I am trying to figure something out. able 1 has only unique calendar dates, table 2 has multiple dates with sale numbers for comparison. These two tables are connected through model.
I would like user to select a date through date slicer( this is currently through date dim) connected to table using model (date as unique key, one to many) and ;
I would like to calculate for 2 other dates bsed on user's selection, previous date, previous week date(to do this, how can it be done?)
Through user selected dates, I am would like to be able to compare day on day, numbers from fact table.
try these measures,
1. Previous Day
Previous day sales =
CALCULATE(
SUM(Sales[Sales]),
PREVIOUSDAY('Date'[Date]))
2. Previous week
To get this done you need year and weekno column in your date dimension table,
Previous week sales =
CALCULATE (
SUM ( Sales[Sales] ),
FILTER (
ALL ( 'Date' ),
'Date'[Year] = MAX ( 'Date'[Year] )
&& 'Date'[Weekno]
= MAX ( 'Date'[Weekno] ) - 1
)
)
Thanks,
Arul
Hey @imminent ,
I recommend reading this article: https://www.daxpatterns.com/time-patterns/
This article comprises almost everything that needs to be known about date calculations.
Hopefully, this helps to tackle your challenge.
Regards,
Tom
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |