The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I would like to create two measures that calculate the total spend for income tax by current year and the total spend for income tax by prior year. I would like the measures to recognize the year we are currently in an update accordingly. So for example since we are in 2022 the current year measure would calculate the sum of income tax spend in 2022 and the prior year measure would calculate the sum of income tax spend for 2021. When 2023 arrives the measures should recognize this and the current year measure should calculate tax spend for the year 2023 and the prior year measure should calculate tax spend for 2022. Can anyone help me with this?
TaxType | Year | Spend |
Income Tax | 2020 | 50 |
vendor tax | 2020 | 40 |
state tax | 2020 | 60 |
Other | 2020 | 70 |
Income Tax | 2021 | 100 |
vendor tax | 2021 | 40 |
state tax | 2021 | 56 |
Other | 2021 | 90 |
Income Tax | 2022 | 20 |
vendor tax | 2022 | 10 |
state tax | 2022 | 20 |
Other | 2022 | 35 |
Solved! Go to Solution.
@dw700d , Create separate year or date table and then try
//Only year vs Year, not a level below
This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
If you want based today
This year Today =
var _min = today()
return
CALCULATE(sum('Table'[Sales]), FILTER('Date','Date'[Date] = year(today()) ) )
Last year Today =
var _min = today()
return
CALCULATE(sum('Table'[Sales]), FILTER('Date','Date'[Date] = year(today())-1 ) )
or
This year Today =
var _min = today()
return
CALCULATE(sum('Table'[Sales]), FILTER(all('Date'),'Date'[Date] = year(today()) ) )
Last year Today =
var _min = today()
return
CALCULATE(sum('Table'[Sales]), FILTER(all('Date'),'Date'[Date] = year(today())-1 ) )
@dw700d , Create separate year or date table and then try
//Only year vs Year, not a level below
This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
If you want based today
This year Today =
var _min = today()
return
CALCULATE(sum('Table'[Sales]), FILTER('Date','Date'[Date] = year(today()) ) )
Last year Today =
var _min = today()
return
CALCULATE(sum('Table'[Sales]), FILTER('Date','Date'[Date] = year(today())-1 ) )
or
This year Today =
var _min = today()
return
CALCULATE(sum('Table'[Sales]), FILTER(all('Date'),'Date'[Date] = year(today()) ) )
Last year Today =
var _min = today()
return
CALCULATE(sum('Table'[Sales]), FILTER(all('Date'),'Date'[Date] = year(today())-1 ) )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
96 | |
80 | |
62 | |
56 |
User | Count |
---|---|
256 | |
120 | |
112 | |
83 | |
71 |