Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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 |
---|---|
79 | |
74 | |
62 | |
61 | |
45 |
User | Count |
---|---|
108 | |
100 | |
91 | |
83 | |
61 |