Forum Discussion

rane19's avatar
rane19
Icon for Helper I rankHelper I
6 years ago

Dynamically calculate difference between columns (Multi Years)Power BI

Situation I have been tried couple answers in power bi forum but not really fit my case as calculation should be dynamically calculate the differences between columns (Multi Years).   After sum up...
Need Help
rane19's avatar
rane19
Icon for Helper I rankHelper I
6 years ago

Pete , 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's avatar
BA_Pete
Icon for Super User rankSuper User
6 years ago

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's avatar
    rane19
    Icon for Helper I rankHelper I
    6 years ago

    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. )