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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tkwh1
Frequent Visitor

Seeking Week calculation with 2 conditions

Hello Community,

First time posting here as I did not see the solution to my problem..

What I need is the following:

 

 

GOAL :  ‘Week over week%’ calculation under 2 conditions:

 

(1)  Picks the most recent week of data available (avoid blank) under the selected filters (‘Year’ & ‘Month’ from the Date table)

(2)  Week over week calculation takes into account the days from both current vs previous week

 

*I want to display the most recent Week-to-date-data on a Card automatically when choosing Year & Month on a slicer. Please see detail below:

 

 

Current environment:

  • Week starts on Sunday to Saturday
  • Current date : May 24, 2023
  • Dashboard is set up so that users will mainly choose only ‘Year’ and ‘Month’.
  • Created a date table (dimDate) , where one of the columns I use mainly for this calculation where current week is ‘0’  :   [Week Relative Offset] = DATEDIFF( TODAY(), dimDate[Date], week)

DatesTable.jpg

 

Requirement (1)

  • The problem I am having is that when I select the current month (May), it returns a Blank, I am assuming because it is pointed to the last week in the current month (May 28th) where there is no data for.
  • I have tried creating a measure ([Week Calc]) giving me the most recent week for 'Total QTY'.  When I select 'May' on the date slicer, I need it to avoid all blank weeks and pick up on the latest week which actually contains data.

card.jpg

****

Week Calc  =

 

 VAR CountWeeks =  MAX ( dimDate [Week Relative Offset] )

 

 RETURN

 CALCULATE(

     [Total QTY],

     dimDate [Week Relative Offset]  =  CountWeeks

 )
****

 

 

Requirement 2

  • Combining with the above Req. 1,  when calculating Week over Week%,  I would also like to incorporate days of that week (current vs previous).  
  • In this example, for the current week  (May 21st total = 1,566) with (Sun = 847) and (Mon=719) data, I want to compare it to the same days from the previous week of May 14 where (Sun = 793) and (Mon = 782).

weeks.jpg

 


So my goal is:

 

WOW% for the current week to date  =  (  (847+719)  –  (793+782) )    /   (793+782)

 

 

 

Thank you very much in advance!! 

1 ACCEPTED SOLUTION
some_bih
Super User
Super User

Hi @tkwh1 see part "Week-to-date over the full previous week" and other related week calculation

from link https://www.daxpatterns.com/week-related-calculations/ 

I hope this help





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






View solution in original post

2 REPLIES 2
some_bih
Super User
Super User

Hi @tkwh1 see part "Week-to-date over the full previous week" and other related week calculation

from link https://www.daxpatterns.com/week-related-calculations/ 

I hope this help





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






hi @some_bih 

Thank you for this link, it seems cover what I need.

Appreciate the help !

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors