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
jaywakeford
Frequent Visitor

Calculate cumulative value 13 weeks prior to selected (slicer selected) date

 

Hello, I am new to DAX

 

Have a table with a week ending date in one field and value in another field. 

 

Relationship is established to a second table that has week ending dates that is used as a slicer for the data.  

 

Trying to return the sum of value between the selected week ending date and the previous 13 weeks inclusive of the selected week.  

 

Step one

 

Consumption = sum('5 Stock Movement Report'[Consumption Value])

 

Step two

 

Last 13 Weeks Consumption = calculate([Consumption],dateadd('5 Stock Movement Report'[Week Ending],-91,DAY))

 

 

Step 2 returns a value of (blank)  

 

I was able to correctly return the date that cooresponds the the 13 weeks prior to the selected date.  Checked this by creating the dateadd measure on its own.  

 

Is there a solution that someone can share that solves this? 

 

Many thanks,

 

 

 

1 ACCEPTED SOLUTION

Hi @jaywakeford

 

Try DatesInPeriod instead: 

DATESINPERIOD( Calendar[Date] , MAX(Calendrier[Date]) , -91 , DAY )

 

It will return a table with the last 91 days from the 2nd argument (included) which you can use as a filter argument in your measures.

View solution in original post

2 REPLIES 2

Hi @jaywakeford

 

Try DatesInPeriod instead: 

DATESINPERIOD( Calendar[Date] , MAX(Calendrier[Date]) , -91 , DAY )

 

It will return a table with the last 91 days from the 2nd argument (included) which you can use as a filter argument in your measures.

Thank you very much for the reply and answer!

Conceptually it seems to make perfect logical sense.   And works perfectly.  

 

 

 

 

 

 

 

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.