cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jaywakeford Frequent Visitor
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

Accepted Solutions
Highlighted
Datatouille Established Member
Established Member

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

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.

2 REPLIES 2
Highlighted
Datatouille Established Member
Established Member

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

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.

jaywakeford Frequent Visitor
Frequent Visitor

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

Thank you very much for the reply and answer!

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

 

 

 

 

 

 

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 106 members 1,401 guests
Please welcome our newest community members: