0

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

Hi

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

3 replies

null
    • imbarmarinescubar_pyram
    • 3 wk 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
    • 3 wk ago
    • Reported - view

    Here is a less formulaic way of doing this. Point and click. Lots of small steps. But uncomplicated.

    1. Right click on the 'quarter' attribute in Discover >> Create Parameter
    2. Right click on the new parameter >> Create List > Standard List.
    3. Right click on the new list and >> Open in Formulate (list editor).
    4. Drag the Union operator from Derive menu into the canvas.
    5. now drag the existing "standard list" element block into the top open square on the left,  feeding into the union.
    6. Drag a new standard list element block from the "Add" menu into the bottom open square feeding the union.
    7. With the new standard block selected still:
      1.  find the quarter attribute in the tree below (likely already selected)
      2. in the element panel on the right, click the small "FX" folder button on the top left.
      3. 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.
      4. in the pop-up choose "previous member"
    8. Save the list.
    9. Go back to discover > new grid. 
    10. 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.
    11. Add another attribute on rows and choose a measure.
    12. From the drop down, choose a different quarter. 

    TADA!

      • NPANS
      • 3 wk 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.

      😉

Content aside

  • Status Answered
  • 3 wk agoLast active
  • 3Replies
  • 33Views
  • 4 Following