Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |