Forum Discussion
Helper IDynamically calculate difference between columns (Multi Years)Power BI
Super UserHi rane19 ,
Try these measures. The PY one is a bit verbose but it was the only way I could get the Prior Year to work:
_valueTY = SUM(dTable[value])
_valuePY =
VAR prevYear =
DATE(
YEAR(MAX(dTable[dtYear])) - 1,
MONTH(MAX(dTable[dtYear])),
DAY(MAX(dTable[dtYear]))
)
RETURN
CALCULATE(
SUM(dTable[value]),
FILTER(
ALLEXCEPT(dTable, dTable[group]),
dTable[dtYear] = prevYear
)
)
_valueDiff = [_valuePY] - [_valueTY]
These measures giveme the following output when applied to a Matrix visual:
Pete
Helper IPete , tons of thank I believe this is the answer but i couldn't reproduce the same output as the _valueDiff columns shows minus of same _vauleTY figures .
I believe the reason is date processing ? as your snapshot shows : 1 - dtYear and 2 - year , which is applied in Matrix table .
Should I add 1 more step to deal with the raw YEAR column ? (like convert from value to string )
DATE(
YEAR(MAX(dTable[dtYear])) - 1,
MONTH(MAX(dTable[dtYear])),
DAY(MAX(dTable[dtYear]))
)
- BA_Pete
Hi rane19 ,
Sorry, I should have included the fact that I duplicated your [Year] column in Power Query and then changed the data type to date type to create the [dtYear] column. When you implement this in Power Query, it will change all of your years to 1st January for that year, but it allows you to use the datetime functions used in the _valuePY measure.
Pete
- rane19
Pete , worked ! you are awesome . (Even I have actually stucked in the step of changing Date in power query that turns my date into 1905/7/17 e.t.c. )