Calculating percentage change from start point by category

[philipnye]

Hi there,

I'm not having much luck working out how to do a certain calculation.

I'm applying a filter to my visual (Outcome Group as one of 'Charged/Summonsed', 'Diversionary, educational or intervention activity...', 'Out-of-court (informal)', 'Out-of-court (formal)') and for each category (Force Name) attemping to calculate percentage change in Outcomes for offences that were recorded in the quarter between each End date in my data and a starting End date - 2014-06-30.

My workbook is available here: https://www.dropbox.com/scl/fi/vr16i5qxbdxq8jx6iuk5l/Police-outcomes-open-data-testing.pbix?rlkey=pb...

And the data here: https://www.dropbox.com/scl/fi/0godipehi6tlriklks2lb/Working-file-police-outcomes-open-data.xlsx?rlk...

This is the DAX query I've written:

Outcomes for investigations closed in the quarter QoQ% = 
VAR _start_value =
    CALCULATE(
        SUM('Collated data'[Outcomes for investigations closed in the quarter]),
        KEEPFILTERS('Collated data'[End date]=DATE(2014,6,30))
    )
RETURN
    DIVIDE(
        SUM('Collated data'[Outcomes for investigations closed in the quarter])
            - _start_value,
        _start_value
    )

But adding this to my visual only adds a single End date column - for 2014-06-14. I don't really understand why - I thought that date would only be applied in the calculation of `_start_date`. And I don't know how to get the calculation working for each subsequent date.

Any help would be appreciated.

Thanks,

Philip

[philipnye]

Hi @Anonymous, @some_bih 

@Anonymous Thank you so much - that solves my problem! Hero ✨

@some_bih Thank you - and sorry my data wasn't available (I posted the wrong version initially and think you must have looked in the window between me taking the old data down and adding a replacement dataset). Thanks for your willingness to help though!

[Anonymous · Accepted solution]

Thanks for the reply from @some_bih , please allow me to provide another insight.
Hi @philipnye ,

You just need to change this line in the measure by removing KEEPFILTERS.

When _start_value is calculated, the internal filter provided by /'Collated data'[End date]=DATE(2014,6,30)/ will override the filter provided by the matrix End date column.

KEEPFILTERS('Collated data'[End date]=DATE(2014,6,30))

The modified measure.

Outcomes for investigations closed in the quarter QoQ% = 
VAR _start_value =
    CALCULATE(
        SUM('Collated data'[Outcomes for investigations closed in the quarter]),
        'Collated data'[End date]=DATE(2014,6,30)
    )
RETURN
    DIVIDE(
        SUM('Collated data'[Outcomes for investigations closed in the quarter])
            - _start_value,
        _start_value
    )

The measure works correctly on subsequent dates.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[some_bih]

Hi @philipnye I could not see your data .I hope you have Calendar / Date table created in Power BI.

Check link for creating it.

If you have your measure should leverage Date table your and your fact table, without part DATE(2014,6,30) in your CALCULATE part.