cancel
Showing results for
Did you mean: Frequent Visitor

## Sameperiodlastyear per category

My first post ever 🙂

Hi,
I have the following table called Transactions from an online shop:

 Order ID Item ID Category Date 444 apple fruit 1-jan-2021 555 pear fruit 2-jan-2021 666 apple fruit 3-jan-2021 777 potato vegetable 4-jan-2021 111 orange fruit 1-jan-2020 222 broccoli vegetable 2-jan-2020 333 cabbage vegetable 3-jan-2020

Now I have created a slicer for Date and a matrix table.
In my matrix table, I have now two first columns below (A&B).
What I don't have is column C, which counts LY values for the date range I chose through my slicer.

 A  Row: Category B Values: Count Item ID C  Values: Count Item ID      (sameperiodlastyear) Fruit 3 1 Vegetable 1 2

Br,
Kudy

1 ACCEPTED SOLUTION  Community Support

Hi @Kudy ,

You could create a seperate year table.

``Table 2 = DISTINCT('Table'[Date].[Year])`` Two measure are created as

``Count Item ID = IF(ISFILTERED('Table 2'[Year]),CALCULATE(COUNT('Table'[Item ID]),FILTER('Table',YEAR([Date])=SELECTEDVALUE('Table 2'[Year]))),COUNT('Table'[Item ID]))``
``Count Item ID(sameperiodlastyear) = IF(ISFILTERED('Table 2'[Year]), CALCULATE(COUNT('Table'[Item ID]),FILTER('Table',YEAR([Date])=SELECTEDVALUE('Table 2'[Year])-1)),COUNT('Table'[Item ID]))`` Best Regards,

Stephen Tao

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

2 REPLIES 2  Community Support

Hi @Kudy ,

You could create a seperate year table.

``Table 2 = DISTINCT('Table'[Date].[Year])`` Two measure are created as

``Count Item ID = IF(ISFILTERED('Table 2'[Year]),CALCULATE(COUNT('Table'[Item ID]),FILTER('Table',YEAR([Date])=SELECTEDVALUE('Table 2'[Year]))),COUNT('Table'[Item ID]))``
``Count Item ID(sameperiodlastyear) = IF(ISFILTERED('Table 2'[Year]), CALCULATE(COUNT('Table'[Item ID]),FILTER('Table',YEAR([Date])=SELECTEDVALUE('Table 2'[Year])-1)),COUNT('Table'[Item ID]))`` Best Regards,

Stephen Tao

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

@Kudy There are two main ways of doing this, using TI functions like SAMEPERIODLASTYEAR or not:

To **bleep** With Time Intelligence - Microsoft Power BI Community

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!     