I am quite a beginner in EazyBI/MDX.
I am already tracking the Remaining estimated hours over time.
I then want to get the value of Remaining estimated hours history at a fixed date: 1 month ago from today. As I am writing, today is Nov.9th, so that would be the value on Oct.9th, so the value I should get is 555.33
I tried this way:
([Measures].[Remaining estimated hours history], [Time].[Day].DateMember(DateParse(‘1 month ago’)))
However that returns nothing…
Can you help me figure out what is not working?
The function DateMember() understands relative dates. That means you don’t need to parse these values additionally with DateParse(). Additionally, you use the “Weekly” hierarchy in Time dimension in the report rows. But in the calculated measure, you reference the default Time dimension hierarchy. With such a configuration, you won’t retrieve the expected results.
You can either address that with
[Time.Weekly].[Day].DateMember or, create a universal approach that will work with all Time dimension hierarchies with
[Time].Currenthierarchy.Levels("Day").Datemember('1 month ago')))').
See more details about DateMember() function here - DateMember.
Roberts // firstname.lastname@example.org