Forum Discussion
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 |
Really appreciate your help!
Br,
Kudy
Hi Anonymous ,
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
- Greg_Deckler
Community Champion
Anonymous There are two main ways of doing this, using TI functions like SAMEPERIODLASTYEAR or not:
To **bleep** With Time Intelligence - Microsoft Power BI Community
Hi Anonymous ,
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.
