cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
naivecalf Regular Visitor
Regular Visitor

Expand rolling data

Hi,

 

I did rolling data by week, for qty_shipped, here is the fomular,

Accumulated Shipped = SUMX(
FILTER(
ALL(Shipped),
Shipped[Week_Shipped] >= 27 &&
Shipped[Week_Shipped]<= MAX(Shipped[Week_Shipped])),
Shipped[Amt_Shipped])
and here is the chart, please see green bar chart, how can I expand it to other date, in this sample, I need show 2.6M for weeks after and on 34.
thanks.
shipped rolling.jpg
1 ACCEPTED SOLUTION

Accepted Solutions
naivecalf Regular Visitor
Regular Visitor

Re: Expand rolling data

@d_gosbell 

 

Thank you a lot, perfect!

I did it. 

Appreciate.

 

rolling data great.jpg

5 REPLIES 5
Super User
Super User

Re: Expand rolling data

The condition stopping this data at week 33 is the following:

 

  Shipped[Week_Shipped]<= MAX(Shipped[Week_Shipped])),

 

So you either need to just remove that conditition or maybe use the same max date as your ToCome , Backlog or Plan measures.

 

naivecalf Regular Visitor
Regular Visitor

Re: Expand rolling data

@d_gosbell 

 

thank you. I tried to remove the condition, it expand to last week, but the total amount calculated a wrong number.

rolling data.jpg

naivecalf Regular Visitor
Regular Visitor

Re: Expand rolling data

then I tried another one,

in the screenshot below,

list 2, calculation rolling is correct but can not expand more weeks.

list1, expand but rolling data is not right. 

 

Accu Shipped3 = SUMX(
FILTER(
ALL(Shipped),
Shipped[Week_Shipped] >= 27 &&
Shipped[Week_Shipped] <= 50),
Shipped[Amt_Shipped])
 

rolling data1.jpg

Highlighted
Super User
Super User

Re: Expand rolling data

Oh yes, I see what that is doing. I don't normally use date columns directly in the fact table for filtering. I usually create a separate date table and then hide the date columns in the fact table. The date table should then have all the dates in the range you want to report on (so maybe all the days in 2019).

 

Then you could build your calculations using the following pattern

 

Accumulated Shipped = CALCULATE(
SUM( Shipped[Amt_Shipped] ) ,
FILTER(
ALL(Date[Week]),
Date[Week] >= 27 &&
Date[Week]<= MAX(Date[Week]))
)
 
naivecalf Regular Visitor
Regular Visitor

Re: Expand rolling data

@d_gosbell 

 

Thank you a lot, perfect!

I did it. 

Appreciate.

 

rolling data great.jpg

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 109 members 1,584 guests
Please welcome our newest community members: