Forum Discussion

Saumyas's avatar
Saumyas
Regular Visitor
3 years ago
Solved

Month over year trend

Hi,  I want to show current FY months data trend with last FY average in a chart.

To be shown like this

FY 2021-22

April 22

May 22

June 22

July 22

August 22

 

Please post any solution for that how to group last FY months in Year.

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Saumyas ,

    You may consider nesting a layer of IF() or SWITCH outside of the measure:

    Measure = 
VAR _LEFT = LEFT(MAX('Date'[Column]),2)
VAR _RESULT = IF(_LEFT="FY", AVERAGE('Table'[Value]),SUM('Table'[Value]))
RETURN
_RESULT

    or 

    Measure 2 = 
VAR _LEFT = LEFT(MAX('Date'[Column]),2)
VAR _RESULT = 
SWITCH(
    _LEFT,
    "FY",AVERAGE('Table'[Value]),
    "Ma",SUM('Table'[Value]),
    "Ap",SUM('Table'[Value]),
    COUNT('Table'[Value2]
)
RETURN
_RESULT

    Please rewrite the formula as needed.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Saumyas ,

    Suppose we have such a financial calendar table:

    Please new a calculated column:

    Column = 
VAR _TODAY =
    TODAY ()
VAR _FY =
    CALCULATE (
        MAX ( 'Date'[Fin Year] ),
        FILTER ( ALL ( 'Date' ), 'Date'[Date] = _TODAY )
    )
VAR _LAST_FY = _FY - 1
VAR _RESULT =
    SWITCH ( 'Date'[Fin Year], _FY, 'Date'[Fin Month], _LAST_FY, 'Date'[FY] )
RETURN
    _RESULT

    and use it with measure in a table visual, and filter out all blank values in the filter.

    Measure:

    Measure = AVERAGE('Table'[Value])

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

    • Saumyas's avatar
      Saumyas
      Regular Visitor
      3 years ago

      Hi, thanks for your reply.

      but by this I will get average of all periods, but I need average of only FY 21-22, and sum or count of other months. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Saumyas ,

        You may consider nesting a layer of IF() or SWITCH outside of the measure:

        Measure = 
VAR _LEFT = LEFT(MAX('Date'[Column]),2)
VAR _RESULT = IF(_LEFT="FY", AVERAGE('Table'[Value]),SUM('Table'[Value]))
RETURN
_RESULT

        or 

        Measure 2 = 
VAR _LEFT = LEFT(MAX('Date'[Column]),2)
VAR _RESULT = 
SWITCH(
    _LEFT,
    "FY",AVERAGE('Table'[Value]),
    "Ma",SUM('Table'[Value]),
    "Ap",SUM('Table'[Value]),
    COUNT('Table'[Value2]
)
RETURN
_RESULT

        Please rewrite the formula as needed.

         

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly -- How to provide sample data