0

Previous Month Value

What is the best way to dynamically display the previous month's value in a table based on the displayed value? The available context calculations only allow for difference from previous, etc and are not editable. 

Expected output is a table with a list of months selected. Should show a measure (default distinct count) and another column displaying the selected measure's value from the previous month (relative to the date on the row, not today). 

6 replies

    • Redeemed from the Dark Side (Power BI)
    • Obee1
    • 8 mths ago
    • Reported - view

    Create a formula in 'Formulate', using a single 'data point'. The Data point should be your measure, then select the date attribute/hierarchy.

    Instead of picking a specific element in the date, click the FX button and then choose previous member from the listing. 

    In script it looks like this (either one works). The wizard above produces the second one.

    ([measures].[transactions Sales],[transactions].[Dates full month name].lag(1))
    
    ([measures].[transactions Sales],[transactions].[Dates full month name].PrevMember)

    • Customer Solutions Architect
    • Moshe_Yossef
    • 8 mths ago
    • Reported - view

    Just adding to  's great answer - This is not a contextual calculation, it is a semantic calculation, as you want it to be based on the data, not on the data shown in the discover.

    The calculation will also work if you choose the month from a filter, for example.

    • Paul_Narup
    • 8 mths ago
    • Reported - view

    Ok, thanks guys, that's what I was attempting to do but was hitting errors. Has anyone seen issues using previous member, etc when using custom columns?

      • Redeemed from the Dark Side (Power BI)
      • Obee1
      • 8 mths ago
      • Reported - view

       It works fine for me. I would suggest you make all the calcs sharable. I think there is added complexity when using some or all as one-off items in a report. 

      • Paul_Narup
      • 8 mths ago
      • Reported - view

        I can confirm my issues are caused by using custom columns. Using columns provided by the DB work fine. Thanks for the sanity check

    • Senior Director of Product
    • Ian_Macdonald
    • 8 mths ago
    • Reported - view

    Hi All,

    There was an identified issue with using Lead(), Lag() and other relative position calculation functions with Custom Columns prior to Pyramid 2023.01.063. 

    If your build number is earlier than 063, download and upgrade your Pyramid installation to correct this issue.

    Hope that helps.

    Ian

Content aside

  • Status Answered
  • 8 mths agoLast active
  • 6Replies closed
  • 96Views
  • 4 Following