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 th...
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

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