Change from PY DAX code is not correct for some records

[dragon2]

Hello,

My measure for "Change from PY" is not correct for some records for 2021 and 2022. For example: Apple, Full, 2022, Change should be -9. Pear, Part, 2021, Change should be -1. Pear, Full, 2022, Change should be -10.

What is wrong with my current DAX code? I created a Calendar table and connected 'Calendar'[Year] to 'Sheet1'[Year].

PY-DAX-is-wrong.pbix

Calendar = CALENDAR(DATE(2020,1,1),DATE(2022,12,31))
Year = YEAR('Calendar'[Date])

newCount = CALCULATE(SUM(Sheet1[Count]))+0

Change from PY = 
VAR
    vCY = MAX('Calendar'[Year])
VAR vPYAmount =
    CALCULATE([newCount],'Calendar'[Year]=vCY - 1)
VAR vCYAmount =
    CALCULATE([newCount])
VAR Result =
    vCYAmount - vPYAmount
RETURN Result

Group	Type	Metric	Count	Year
A	Apple	Full	5	2020
A	Apple	Full	9	2021
A	Apple	Part	2	2022
B	Pear	Part	1	2020
B	Pear	Full	10	2021

[Ashish_Excel]

Hi,

Download the PBI file from here.

Hope this helps.

[Elena_Kalina]

Hi, @gmsamborn 

Using your sample data as a reference, I created a simple table. Then, to generate the column you need, I added a calculated column using the following formula:

Change from PY =

VAR CY_Type = 'Fruit'[Type]

VAR CY = 'Fruit'[Year]

VAR CY_Metric = 'Fruit'[Metric]

VAR CY_Amount ='Fruit'[NewCount]

VAR PY_Amount_ =

    CALCULATE(

        SUM('Fruit'[NewCount]),

        FILTER(

            ALL('Fruit'),

            'Fruit'[Type] = CY_Type && 'Fruit'[Metric] = CY_Metric &&

            'Fruit'[Year] = CY - 1

        )

    )

VAR PY_amount = IF(ISBLANK(PY_Amount_),

                   0,

                   PY_Amount_

                )

RETURN

CY_Amount - PY_amount

I believe this should give you exactly what you were looking for.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[gmsamborn · Accepted solution]

Hi @dragon2 

The DAX isn't the issue.. 

The problem is with your many-to-many relationship on [Year].  

Instead I created a caculated column on your Sheet1 table:    Date = DATE( [Year], 1, 1 )

Use that in your relationship with your date table in a simple one-to-many relationship.

Let me know if you have any questions.

[dragon2]

Thank you so much.

For some reason, with my acutal data, this was not working. So, instead of making the Calendar table that I shared originally, I made a new table like this:

newCalendar = DISTINCT(Sheet1[Year])

Then I made a 1-to-many relationship on this table and sheet1[Year]. It worked perfectly after that.