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,
I would like to write a measure that would be 13-4-24=-15.
It should be calculated based on (13 is the total value for redeveloped&high) - (4 is the total value for redeveloped&high for the previous year) - (24 is the total value for financial&high)
Date | Country | Type | Volume | Total |
Nov-22 | DE | Redeveloped | Low | 12 |
Nov-22 | DE | Redeveloped | High | 13 |
Nov-22 | DE | Financial | Low | 21 |
Nov-22 | DE | Financial | High | 24 |
Nov-22 | DE | Source | Low | 34 |
Nov-22 | DE | Source | High | 35 |
Nov-21 | DE | Redeveloped | Low | 3 |
Nov-21 | DE | Redeveloped | High | 4 |
Nov-21 | DE | Financial | Low | 5 |
Nov-21 | DE | Financial | High | 2 |
Nov-21 | DE | Source | Low | 6 |
Nov-21 | DE | Source | High | 9 |
Can you help me to write a measure for this? I want to use it for a chart to show the periods. I have 2 millions of records.
Thanks
Solved! Go to Solution.
Yes, you somehow have to indicate what year "this year" is. If you don't do that through a date slicer or filter the visual or report page itself it needs to be done in another way.
It shouldn't be more complicated than to create a new variable that tells the next what "this year" is, and then use it as an additional filter condition later on:
VAR __ThisYear =
-- some type of measure
VAR __ThisYearRedeveloped =
CALCULATE(
SUM(Table[Total]),
Table[Type] = "Redeveloped",
Table[Volume] = "High",
YEAR(Table[Date]) __ThisYear
)
So the question is how you define "this year". Is it TODAY(), i.e. 2023 now? Is it MAX(Table[Date]), i.e. 2022 if that is the latest data you have? Is it something else? That logic needs to be added.
Hi!
This year's redeveloped and financial should be a simple CALCULATE(). Last years value requires you to indicate what last year. If you have a date table, SAMEPERIODLASTYEAR() should work, but otherwise you need to find a different solution (e.g. store this year's year as a variable and then use ALL() and filter out this year's year - 1).
Something like this might help you going forward.
Measure :=
VAR __ThisYearRedeveloped =
CALCULATE(
SUM(Table[Total]),
Table[Type] = "Redeveloped",
Table[Volume] = "High"
)
VAR __LastYearRedeveloped =
CALCULATE(
_ThisYearRedeveloped,
SAMEPERIODLASTYEAR(Dates[Date]) //Adjust according to your table names
)
VAR __ThisYearFinancial =
CALCULATE(
SUM(Table[Total]),
Table[Type] = "Financial",
Table[Volume] = "High"
)
return
__ThisYearRedeveloped - __LastYearRedeveloped -__ThisYearFinancial
Hope this helps!
Hi Thanks for the help, but it is not working. When I tried the measure, it is not exactly counting as I explained. If i don't select the date, it counts for the whole records. For example;
Yes, you somehow have to indicate what year "this year" is. If you don't do that through a date slicer or filter the visual or report page itself it needs to be done in another way.
It shouldn't be more complicated than to create a new variable that tells the next what "this year" is, and then use it as an additional filter condition later on:
VAR __ThisYear =
-- some type of measure
VAR __ThisYearRedeveloped =
CALCULATE(
SUM(Table[Total]),
Table[Type] = "Redeveloped",
Table[Volume] = "High",
YEAR(Table[Date]) __ThisYear
)
So the question is how you define "this year". Is it TODAY(), i.e. 2023 now? Is it MAX(Table[Date]), i.e. 2022 if that is the latest data you have? Is it something else? That logic needs to be added.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |