Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Everyone,
Currently i am busy with making cards to show the sales of the current and previous week. My formula works fine except if there is a year transition. The previous week card shows (blank) instead of the correct value. I am using the following formula to calculate the sales of previous week:
Solved! Go to Solution.
Hello @Jop1235123,
Can you please try this revised DAX:
Sales Last Week =
VAR CurrentWeek = WEEKNUM(TODAY(), 21)
VAR CurrentYear = YEAR(TODAY())
VAR PreviousWeek = IF(CurrentWeek = 1, CALCULATE(MAX('Date'[Week]), ALL('Date'), 'Date'[Year] = CurrentYear - 1), CurrentWeek - 1)
VAR PreviousYear = IF(CurrentWeek = 1, CurrentYear - 1, CurrentYear)
RETURN
SUMX(
FILTER(
ALL('Date'),
'Date'[Week] = PreviousWeek && 'Date'[Year] = PreviousYear
),
[Total Sales]
)
In your calendar table include a column that clearly indicates the year-week. Then use the current filter context to find the largest year-week that is smaller than your "current" one.
Hello @Jop1235123,
Can you please try this revised DAX:
Sales Last Week =
VAR CurrentWeek = WEEKNUM(TODAY(), 21)
VAR CurrentYear = YEAR(TODAY())
VAR PreviousWeek = IF(CurrentWeek = 1, CALCULATE(MAX('Date'[Week]), ALL('Date'), 'Date'[Year] = CurrentYear - 1), CurrentWeek - 1)
VAR PreviousYear = IF(CurrentWeek = 1, CurrentYear - 1, CurrentYear)
RETURN
SUMX(
FILTER(
ALL('Date'),
'Date'[Week] = PreviousWeek && 'Date'[Year] = PreviousYear
),
[Total Sales]
)
Thank u very much Sahir.
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |