Story point history difference between selected days

[Andreia Lopes]

Hi,
I am trying to retrieve the weekly trend for my data. I have set a time filter to just show dates corresponding to Wednesdays and I calculate the story point history for each Wednesday (SP Total). So I would like the difference in story points history between Wednesday in one row and the one previous to it (e.g. between Jan 26 and Jan 19 should be 91 and for Jan 19 it should be null since there aren’t any dates considered before Jan 19).I have tried the solution to post Difference between the previous data and the current data, but it just gives me the project story points history total for the day.
Wonder why it doesn’t work for my case. Probably it might have to something with using VisibleRowSet().
Thank you!

[Zane eazyBI Support · Accepted solution]

I believe @Andreia Lopes already found the answer and solution. Anyhow, here is solution, If anyone else getting a similar use case.

It is hard to tell how the report in the screenshot works as it has calculated measures. Based on the description I understand you would like to see the delta of “Story Points history” in the selected Wednesday and “Story Points history” week ago (also Wednesday).

"Story Points history" shows how many story points were at the end of the selected period. To get the delta between two consequent Wednesdays, you might want to subtract “Story Points history” for two specific periods. The tricky part is to find and include in the calculation the “Time” member representing Wednesday 7 days before the day on the report row.

The expression to get Story Point changes from the previous Wednesday to the selected Wednesday might look like this:

--SP value at the end of the selected day
[Measures].[Story Points history] 
-
--subtract SP value one week ago on Wednesday
([Measures].[Story Points history],
--get the Time member from the Weekly hierarchy representin previous Wednesday
Tail(
  Filter(
    --get dates of the middle of previous week
    [Time.Weekly].[Day].DateMembersBetween(
      DateAddDays([Time.Weekly].CurrentHierarchyMember.StartDate,-8),
      DateAddDays([Time.Weekly].CurrentHierarchyMember.StartDate,-6) ),
    --check which is Wednesday
    [Time.Weekly].CurrentMember.Get('Week day') = 3 ), 
  1).Item(0)
)

Note this expression is constructed for the Weekly hierarchy on report rows. If you use the default Time hierarchy for the report, replace [Time.Weekly] with [Time].

More details on tuple construction and used functions to find the previous Wednesday are here:

• https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/calculated-measures
• https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference

Best,
Zane / support@eazyBI.com

[Andreia Lopes]

Thank you for the help @Zane eazyBI Support   :)

[Zane eazyBI Support]

Hi @Andreia Lopes

Could you please share the report definition so I could see the repot context and how you built the calculations?

If you do not want to share the report definition with the community, feel free to send it to support@eazyBI.com, I will get it. 

Best,
Zane / support@eazybi.com

[Fabian Lim]

Hi @Andreia Lopes

@Zane eazyBI Support may be able to help on this one.