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.
Hi all,
i have 3 years data, if i select 2020 , 2020 data and 2019 needed,
if i select 2019 , 2019 data and 2018 data needed
based on year selection last year data needed
sample data
Year | Value | Business |
2019 | 73,431.49 | Allocated |
2019 | 21,088.96 | Consumer |
2019 | 11,203.00 | Consumer |
2019 | 58.07 | Expenses |
2020 | 13,234.34 | Expenses |
2020 | 75,690.32 | Allocated |
2020 | 17,817.28 | Consumer |
2020 | 46.46 | Expenses |
2018 | 71,822.32 | Allocated |
2018 | 18,293.38 | Consumer |
2018 | 69,064.75 | Expenses |
i need result like this
business | CY Value | PY Value |
Allocated | 71,822.32 | 73,431.49 |
Consumer | 17,817.28 | 32,291.96 |
Expenses | 69,111.21 | 13292.21 |
Many thanks
Rajesh
Solved! Go to Solution.
Hi @Anonymous ,
Based on your description, you can create these two measures:
CY Value = SUM ( 'Table'[Value] )
PY Value =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Year]
= SELECTEDVALUE ( 'Table'[Year] ) - 1
&& 'Table'[Business] IN DISTINCT ( 'Table'[Business] )
)
)
Attached a sample file in the below, hopes to help you.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Based on your description, you can create these two measures:
CY Value = SUM ( 'Table'[Value] )
PY Value =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
'Table'[Year]
= SELECTEDVALUE ( 'Table'[Year] ) - 1
&& 'Table'[Business] IN DISTINCT ( 'Table'[Business] )
)
)
Attached a sample file in the below, hopes to help you.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Create a separate year(Date) Table
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
rolling 2 = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) )
@amitchandak thanks for your response
you mean create the date table then create the measure on sales table ?
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |