Dynamic calculation measure for Prev Qtr End using selected date of report

Peter_Perera
10 mths ago
3replies
Answered

I am wondering how to get the following functionality in a report

Run the report for one Date (user select this date through a prompt when running report)

The report should bring data for Date selected and Previous Quarter End Date

Category	Quantity - Date	Quantity - Prev Qtr End
PAR	100	120
CVE	250	200
GHK	322	300
LMN	453	400
ASD	234	230
FGE	123	120

thx

- imbarmarinescubar_pyram
- 10 mths ago
- Reported - view
Hi,

Assuming the Quantity is a measure in the model, and that you have the report filtered using a date column, giving you the first 2 columns in the report, then you can use this expression as a custom measure for the last column:

([measures].[Quantity], [date].[date col].currentMember.ParallelPeriods(1, "QUARTERS", "FULL_QUARTER").lastElement)

It means, get the Quantity value for: get the date member in the current scope -> go back 1 quarter and get the list of all the members for the quarter -> get the last member for the list.

Imbar
- Redeemed from the Dark Side (Power BI)
- Obee1
- 10 mths ago
- Reported - view
Here is a less formulaic way of doing this. Point and click. Lots of small steps. But uncomplicated.

Right click on the 'quarter' attribute in Discover >> Create Parameter

Right click on the new parameter >> Create List > Standard List.

Right click on the new list and >> Open in Formulate (list editor).

Drag the Union operator from Derive menu into the canvas.

now drag the existing "standard list" element block into the top open square on the left, feeding into the union.

Drag a new standard list element block from the "Add" menu into the bottom open square feeding the union.

With the new standard block selected still:

find the quarter attribute in the tree below (likely already selected)

in the element panel on the right, click the small "FX" folder button on the top left.

from the slide up panel hover the quarter parameter (but do not check its box). On the far right of the parameter name click the small 'fx' button.

in the pop-up choose "previous member"

Save the list.

Go back to discover > new grid.

Check/select the new quarter list from the quarter tree. Once the attribute is in the drop zones (wherever it goes), drag the blue chip to the columns of a grid.

Add another attribute on rows and choose a measure.

From the drop down, choose a different quarter.

TADA!
- NPANS
  
  10 mths ago
  
  Reported - view
  Yikes! You can do this using the time calc wizard in 4 clicks.
  
  In Discover, use the 'time calculations' wizard on the query tab of the ribbon
  
  in the drop down, choose the quarter hierarchy
  
  in the dialog, choose "Previous Quarter on Quarter". also check the parameterize button
  
  you'll see the listing in the tree. check the list (not the other things).
  
  then build your report like you described above, with the quarter hierarchy on columns etc.