Item Category missing from PY calculation when category not present in current year

[sgeheeb]

Hi, I am having some trouble with a measure, which has been giving me a headache.

Background: I have a customer who works in the fashion business. When we are comparing this year's numbers to last year's, we cannot simply shift back the date by a year, but, when filtering by item, also the item season.

For example, if today is 8/4/2024 and we are filtering for items of season "W24" (Winter 2024), the measure for the previous year has to filter for "W23" (Winter 2023), since "W24" did not exist in 2023.

I have added a calculated column "Previous Season" to the item table. This holds the the item's season equivalent for the previous year. So if the item is "W24", "Previous Season" is 2023. This is working fine.

Now, I am using this measure to calculate the previous year:

Amount PY = 
CALCULATE(
        [Amount],
        'Item'[Season] IN VALUES('Item'[Previous Season]),
        DATEADD('Date'[Date], -1, YEAR)

    )

The [Amount] measure is a simple sum over a column.

The above measure returns the correct total. However, when filtering by additional item attributes (Like "Item Category") one line is missing from the previous year calculation. So for the previous year, the sum of the rows and the total does not add up:

This item Category has sales in 2023 but not in 2024. Again, the total is correct.

If I copy the page and shift the filters back (Manually selecting Season "W23" and setting the date filter to 8/4/2023) I see that the category is there and the total is still correct. So the numbers are only missing from the previous year calculation.

I have tried creating a dummy item with season "W24" and Item Category "Accessiors". When I do this, the row shows up. However, I don't want to use this approach because I would have to create dummy items for every possible combination of season and category. I would have to do the same for every other attribute of the item table, because the problem is the same (e.g. when I filter by "Item Group", I only the the items groups which exist for season "W24")

Any inupt would be greatly appreciated, thanks

[sgeheeb]

Hi,

unfortunately, I cannot share the .pbix and I cannot seem to find the time to create a demo dataset. 

For the time being, I have fixed the issue by introducing dummy items for the empy combination (e.g. Season "W24" and Category == "Accessoirs". This is how I went about it:

1. Use Power Query

2. Create a table with the distinct values of Item.Season

3. Create a table with the distinct values of Item.Category

4. Perform a Cross Join of the two tables

5. Append the result to the Item table

Just in case someone needs a similar workaround.

I am still not happy with the way I have to solve this and I will keep investigating. There has to be some way of achieving the result throught the measure, not Power Query.

I will let you know when I find a solution.

Thank you!

[v-nuoc-msft]

Hi @sgeheeb

I have made some changes to your code:

Amount PY = 
var _Year = YEAR(SELECTEDVALUE('Date'[Date])) - 1
RETURN
CALCULATE(
    SUM('Item'[Amount]),  
    FILTER(
        ALL('Item'),
        YEAR('Item'[Date]) = _Year
        &&
        'Item'[Category] IN VALUES('Item'[Category])
        )
)

Here is the result.

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

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

[sgeheeb]

Hello @v-nuoc-msft , thanks for your reply, but this does not solve the issue. I get the same amount for all Item Categories (due to the ALL()) and we are missing the part with the item season being shifted back. Funny enough, the missing item category still does not show up.

In my mind, it should work like this: I select Item Season "W24" and a measure for the amount. There are no items with the combination of Season == "W24" and Category =="Accessoirs", so I don't get a row for "Accessoirs". 

Then, I add a measure for the previous year. For this measure, the Season is "W23". There are items with the combination of Season == "W23" and Category == "Accessoirs".

So I would expect to get BLANK for 2024 and a value for 2023.

But it seems like the row is being filtered out because the dimension table is filtering itself and since the combination does not exist in 2024, the row just won't show.

Anyway, I will try to create some sample data soon. Thanks!