Forum Discussion

ShrimpCreole's avatar
ShrimpCreole
Helper I
3 years ago
Solved

Switching Between Periods Presented Using Rolling-12 Summations

I can not for the life of me figure how to write a measure where I can show rolling-12 revenue from month to month and provide the user the ability to select the period to view it in (2, 3, 5, 10 year).

 

Help!

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    3 years ago

    Hi， ShrimpCreole 

    Try to enter a new table :

     

    YTD =
CALCULATE ( SUM ( Sales[Sales Amount] ), DATEADD ( 'Date'[Date], -1, YEAR ) )
    13M =
CALCULATE ( SUM ( Sales[Sales Amount] ), DATEADD ( 'Date'[Date], -13, MONTH ) )

     

    ....

    Then add a new measure like:

    New measure=
SWITCH (
    SELECTEDVALUE ( 'Slicer Table'[Period] ),
    "YTD", [YTD],
    "13M", [13M],
    "2YR", [2YR],
    "3YR", [3YR],
    "5YR", [5YR],
)

    Best Regards,
    Community Support Team _ Eason

     

3 Replies

  • Shaurya's avatar
    Shaurya
    Memorable Member
    3 years ago

    Hi ShrimpCreole,

     

    I am trying to figure out precisely, what you want. I get that you need cumulative revenue across months. Care to elaborate how you want the period selection to work?

    • ShrimpCreole's avatar
      ShrimpCreole
      Helper I
      3 years ago

      Sorry I did a poor job of explaining my issue.

      I want the user to have the ability to select the viewing period using the attached visual.   I can do this when I display monthly revenue totals, but do not know how to do so showing roll-12 totals.  The chart would provide monthly figures over the period.

       

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        3 years ago

        Hi， ShrimpCreole 

        Try to enter a new table :

         

        YTD =
CALCULATE ( SUM ( Sales[Sales Amount] ), DATEADD ( 'Date'[Date], -1, YEAR ) )
        13M =
CALCULATE ( SUM ( Sales[Sales Amount] ), DATEADD ( 'Date'[Date], -13, MONTH ) )

         

        ....

        Then add a new measure like:

        New measure=
SWITCH (
    SELECTEDVALUE ( 'Slicer Table'[Period] ),
    "YTD", [YTD],
    "13M", [13M],
    "2YR", [2YR],
    "3YR", [3YR],
    "5YR", [5YR],
)

        Best Regards,
        Community Support Team _ Eason