0

I have raw data with columns "Year", "Period", "Country", "Sales Amount". Please note there are 13 periods in a year, (and not 12). The dates also differ slightly between periods each year so I don't think TIME Intelligence functions like sameperiodlastyear will work.

I want to create a measure(s) to calculate the % difference in sales amount between a year and the previous year, allowing for a filter on a country to slice the data accurately.

I have currently created 2 measures. When I use the 2nd measure in a graph or matrix, the result is accurate when they're are not any filters selected. As soon as I filter on a specific country, the results on the graph or matrix is inaccurate.

The issue relates to FILTER CONTEXT.

I have created 2 measures:

Sales Amount PY = 
VAR CurrentYear = MAX('raw data'[Year])
VAR CurrentPeriod = MAX('raw data'[Period])
RETURN
CALCULATE(
    [Sales Amount],
    FILTER(
        ALL('raw data'),
        'raw data'[Year] = CurrentYear - 1 &&
        'raw data'[Period] = CurrentPeriod
    )
)



% Difference vs PY = Divide([Sales Amount] - [Sales Amount PY],[Sales Amount PY])

Attached is the results I was expecting - I used excel to illustrate using Spain and France as examples. The matrix and line graph illustrated is what I am trying to recreate in Power BI.

Required Results

Example raw test data

Improve this question
Related questions
1
Year over Year difference in Power BI matrix with year filter
0
How to make a measure showing last year value work with week, qtr and year columns in matrix visual?
1
Power Bi - Total for Period Year Prior Based On SelectedValue Period
Load 6 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.