cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Card visual with the sales of the previous week

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:

Sales last week =
VAR Currentweek = WEEKNUM(TODAY(),21)
VAR Currentyear = YEAR(TODAY())
VAR Maxweek = CALCULATE(MAX('Date'[Week]), ALL('Date'))
RETURN
SUMX(FILTER(ALL('Date'), IF(Currentweek=1,
'Date'[Week]=Maxweek && 'Date'[Jaar]=Currentyear -1,
'Date'[Week] = Currentweek -1 && 'Date'[Jaar] = Currentyear)), [Total sales])

Beside this formula i made of course a date table with week and year column. Because currently it is week 7 i changed this part of the formula SUMX(FILTER(ALL('Date'), IF(Currentweek=1, in SUMX(FILTER(ALL('Date'), IF(Currentweek=7 to test if it shows the sales of the last week in the previous year (2023-52 total sales 5000). However it is showing blank and i dont get what goes wrong.

Hopefully someone has the correct solution, thanks in advance:).

Gr,

Jop
1 ACCEPTED SOLUTION
Super User

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]
)
``````

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
3 REPLIES 3
Super User

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.

Super User

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]
)
``````

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Frequent Visitor

Thank u very much Sahir.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors