Calculate the difference between two dynamically selected date in pages

Questions & Answers
,
1

Hello everyone,

I try to build a report which shows basicly the actual worklogs by filtering two different months. In pages Time dimension is used and with multiple selection two months must be possible to be selected. As a third column I need to calculate a measure that would bring the difference between actual works which are logged in these two selected months. Please see the picture below:

Screenshot_1
Screenshot_11118×512 29.3 KB

I’d appreciate if you could advice me some other ways than using the “Time ago” function in Add calculated feature or using the Time dimension in rows and calculating the difference with “PreviousRowValue” :pray:

1 Like
2

Is there anyone who could help :pray:

3

Hi @Unity ,

You might try the following expression that checks the displayed Time members and delivers the difference for the latest one.

CASE WHEN
 [Time].CurrentMember.Name = 
 Generate(
   Tail(
  Generate(
   VisibleColumnsSet(),
   [Time].CurrentMember
   ),
  1),
  [Time].CurrentMember.Name,
  ",")
THEN
Sum(
 Tail(
  Generate(
   VisibleColumnsSet(),
   [Time].CurrentMember
   ),
  1),
 [Measures].[Issues created])
 -
Sum(
 Head(
  Generate(
   VisibleColumnsSet(),
   [Time].CurrentMember
   ),
  1),
 [Measures].[Issues created])
END

regards,
Oskars / support@eazyBI.com

1 Like
4

Hi Oskars,

Thank you for your kind return.

The Time filter also applies to the measure you suggested. Is there a way to escape this issue? Maybe by escaping the filter, your measure could show the desired data?

Screenshot_1
Screenshot_1808×450 17.2 KB

5

Hi,

You might use your measure “Actual Days” instead of “Issues created” in the expression.

This measure only returns the value for the last selected month. You might enable
Hide empty->Columns
to only show the last month for this measure.

The empty result might also come if you are using a different hierarchy within the Time dimension.
You might replace CurrentMember with CurrentHierarchyMember in the expression.

The updated calculation might be as follows.

CASE WHEN
 [Time].CurrentHierarchyMember.Name = 
 Generate(
   Tail(
  Generate(
   VisibleColumnsSet(),
   [Time].CurrentHierarchyMember
   ),
  1),
  [Time].CurrentHierarchyMember.Name,
  ",")
THEN
Sum(
 Tail(
  Generate(
   VisibleColumnsSet(),
   [Time].CurrentHierarchyMember
   ),
  1),
 [Measures].[Actual Days])
 -
Sum(
 Head(
  Generate(
   VisibleColumnsSet(),
   [Time].CurrentHierarchyMember
   ),
  1),
 [Measures].[Actual Days])
END

If this still returns empty, please send your report definition over to support e-mail to see the actual calculations behind your measures.

Regards,
Oskars / support@eazyBI.com