Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Jop1235123
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
Sahir_Maharaj
Super User
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 13K+ 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
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

View solution in original post

3 REPLIES 3
lbendlin
Super User
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.

Sahir_Maharaj
Super User
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 13K+ 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
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Thank u very much Sahir. 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.