Cumulative annual sum that resets annually from chosen month

[millieturvey]

Hi there,

I am building a dashboard that tracks water consumption. 

I have a dates column and a column for water consumption. 

This graph here shows the annual water consumption from

1st July 2018 - 30th June 2019

1st July 2019 - 30th June 2020 etc. 

I would like to be able select a month in a slicer and see this same visualisation but from any given month.

I also have these cards above the visualisations which I would like to get rid of and have data labels instead. They are calculating the YoY change using this dax

FY24 Q1 YoY Total Water =

VAR __BASELINE_VALUE = [FY23 Q1]

VAR __VALUE_TO_COMPARE = [FY24 Q1]

RETURN

    IF(

        NOT ISBLANK(__VALUE_TO_COMPARE),

        DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)

    )

FY23 Q1 =

CALCULATE(

    SUM('Water'[Total water Used]),

    'Master date table'[Reporting Period 1] IN { "FY23 Q1" }

)

FY24 Q1 =

CALCULATE(

    SUM('Water'[Total water Used]),

    'Master date table'[Reporting Period 1] IN { "FY24 Q1" }

)

I have read lots of forums but can't seem to find exactly what I'm trying to do here. Any help would be massively appreciated.

[v-yiruan-msft · Accepted solution]

Hi @millieturvey ,

You can follow the steps below to get it:

1. Create a measure as below to get the YoY

Measure =
VAR _selperiod =
    SELECTEDVALUE ( 'Master date table'[Reporting Period 1] )
VAR _py =
    MID ( _selperiod, 3, 2 )
VAR _pyvalue =
    CALCULATE (
        SUM ( 'Water'[Total water Used] ),
        'Master date table'[Reporting Period 1]
            = "FY"
                & ( VALUE ( _py ) - 1 ) & " Q1"
    )
VAR _cyvalue =
    CALCULATE (
        SUM ( 'Water'[Total water Used] ),
        'Master date table'[Reporting Period 1] = _selperiod
    )
RETURN
    IF ( NOT ISBLANK ( _cyvalue ), DIVIDE ( _cyvalue - _pyvalue, _pyvalue ) )

2. Create a line and column chart to replace the column chart with the below Fields settings

Line and column chart

Toggle on "Data labels"

Or you can put the measure onto the Tooltips option of column chart.

Best Regards