% Change of specific year over another specific year

[Anonymous]

Hi Guys,

Im calculating Water Consumption

I want to know the % change from a base year like 2008

and if i click on 2020, ill know the % difference from 2008

i only know how to do YoY which can be calculated by changing the period in the quick measure

but if i do say 10 year period, it will calculate 2018 v 2008, 2019 v 2009, 2020 v 2010

which isnt right.

it needs to be fixed to one specific year

Thanks and Regards,

VanCe

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

I think you could use dateadd function to calculate the values of 2008,2009 or 2010.

Measure = CALCULATE([Total Water Consumption],DATEADD('Data Water'[Month],-10,YEAR))

 Or 

Create a filter table and use it change year.

Filter Table = DISTINCT('Data Water'[Year])

Selected Year = SELECTEDVALUE('Filter Table'[Year])

Measure =
CALCULATE (
    [Total Water Consumption],
    FILTER ( 'Data Water', 'Data Water'[Year] = [Selected Year] - 10 )
)

I create a few measures to calculate selected year(2020) VS 2017. Please have a try.

Total Water Consumption_ Selected Year =
CALCULATE (
    [Total Water Consumption],
    FILTER ( 'Data Water', 'Data Water'[Year] = [Selected Year] )
)

Total Water Consumption_ 2017 =
CALCULATE (
    [Total Water Consumption],
    DATEADD ( 'Data Water'[Month], -3, YEAR )
)

Total Water Consumption_ 2017_1 =
CALCULATE (
    [Total Water Consumption],
    FILTER ( 'Data Water', 'Data Water'[Year] = [Selected Year] - 3 )
)

YOY =
DIVIDE (
    [Total Water Consumption_ Selected Year] - [Total Water Consumption_ 2017],
    [Total Water Consumption_ 2017]
)

[Anonymous]

Thank you Xue Ding,

The examples are very helpful!

Regards,

VanCe

[AntrikshSharma]

use something like:

Measure =
DIVIDE (
    [TotalWaterConsumed],
    CALCULATE ( [TotalWaterConsumed], Dates[Year] = 2008 )
)

[Anonymous]

Hi Antriksh Sharma,

Thanks for your reply, do you have quick measure ways to do it ? im not sure about dax yet. I wanted like we can compare like 2020 to 2017 using the slicers by choosing.

Thanks for your help!

Regards,

VanCe

[Anonymous]

Hi,

Can i use the quick measure % difference and then edit, basically i just add in the calculate in the filter part:

Usage % difference from Usage =

VAR __BASELINE_VALUE = SUM('Domestic'[Usage])

VAR __VALUE_TO_COMPARE = calculate(SUM('Domestic'[Usage]),'Calendar'[Year]=2017)

RETURN

    IF(

        NOT ISBLANK(__VALUE_TO_COMPARE),

        DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)

    )

Does it make sense? im comparing currently selected year to 2017's usage.

Thanks and Regards,

VanCe

[AntrikshSharma]

There is no real benefit of using quick measure over the ones created manually but if it suits your need then it is fine 🙂

[Anonymous]

Hi Antriksh Sharma,

Im calculating % Growth. I have created the 'Total Water Consumption' Measure:

Total Water Consumption = sum('Data Water'[DomesticConsumption])

and also i did for the %growth but as my date is not from an individual table, i have trouble retrieving them in the code, and i dont understand the date part you mentioned above, it looks too complicated to me

% Growth = 
DIVIDE (
 CALCULATE ( [Total Water Consumption], ALLSELECTED ( 'Data Water'[Year] ) )
 CALCULATE ( [total water consumption], 'Data Water'[month] = 2017 )
)

Would love to learn code using DAX, but had too many syntaxes errors and the language seems a bit different from VBA.

Would like to know how to i post a link, i posted up and error my whole message typed is gone. Would like to submit this first.

Thanks and Regards,

VanCe

[Anonymous]

Why does my link gets deleted away ? ☹️

[Anonymous]

https://hkland-my.sharepoint.com/:u:/p/zhide_goh/ERzCCBOhZaBLh5QjiStHWykBL6BPtjUW8vpVteco5nLr0g?e=k5...

[Anonymous]

Hi here my link

https://hkland-my.sharepoint.com/:u:/p/zhide_goh/ERzCCBOhZaBLh5QjiStHWykBL6BPtjUW8vpVteco5nLr0g?e=k5... 

[AntrikshSharma]

If you meant the quick measures that allow one to write measure quickly then take my word, don't use them, you won't learn much, first create a measure that sums the Water consumed column, also create a date table, you can use this code to create a separate date table and then use that in the slicer and create a relationship between this date table and your water table based on dates.

Dates =
VAR MinDate =
    YEAR ( MIN ( Sales[Order Date] ) ) //Replace Sales with any table with dates
VAR MaxDate =
    YEAR ( MAX ( Sales[Order Date] ) ) //Replace Sales with any table with dates 
VAR CalendarStartDate =
    DATE ( MinDate, 1, 1 )
VAR CalendarEndDate =
    DATE ( MaxDate, 12, 31 )
VAR DateList =
    CALENDAR ( CalendarStartDate, CalendarEndDate )
VAR Result =
    GENERATE (
        DateList,
        VAR StartDate = [Date]
        RETURN
            ROW (
                "MonthNumber", MONTH ( StartDate ),
                "Month Name", FORMAT ( StartDate, "MMMM" ),
                "Year", YEAR ( StartDate ),
                "Year Month", FORMAT ( StartDate, "YYYY MM" ),
                "Quarter", FORMAT ( StartDate, "\QQ" )
            )
    )
RETURN
    Result

Total Water Consumed = SUM ( Table[Water Column] )

The below measure is for % growth:

Measure =
DIVIDE (
    CALCULATE ( [TotalWaterConsumed], ALLSELECTED ( Dates[Year] ) )
    CALCULATE ( [TotalWaterConsumed], Dates[Year] = 2017 )
)

if you need to calculate the difference then

Difference = 
CALCULATE ( [TotalWaterConsumed], ALLSELECTED ( Dates[Year] ) ) 
- CALCULATE ( [TotalWaterConsumed], Dates[Year] = 2017 )

 If you still need help can you please upload the file.

[Anonymous]

Hi AntrikshSharma,

Thanks for your advice and reply, will study your post, will upload if i still cant do it. Whats the benefit of between creating a seperate table and without? still cant understand they seem to come out the same results.

Thanks and Regards,

VanCe

[AntrikshSharma]

The benefits of separate date table are that it is available in the model separately, you can connect one date table to many other tables, you can have as many columns in a date table as you want.

but in case of Auto date time, you are just limited to the table provided by power bi, if you are slicing data by dates then you can only slice the data for the table from which the date table was created by PBI you can't use any measure that calculates something from other tables. You can't format these columns

[Anonymous]

Hi Antriksh Sharma,

Im calculating % growth, im able to create a [Total water Consumption] measure:

Total Water Consumption = sum('Data Water'[DomesticConsumption])

and i did a % growth measure as you advice, but im stucked at the dates part as im not creating from a date table and do not know how to retrieve them. Would love to learn to use DAX to code, but i got stucked at syntaxes error alot, seems a bit different from VBA.

% Growth = 
DIVIDE (
 CALCULATE ( [Total Water Consumption], ALLSELECTED ( 'Data Water'[Year] ) )
 CALCULATE ( [total water consumption], 'Data Water'[month] = 2017 )
)

Please help me. i have shared the link to the file, im not sure how to attach the pbix file here. Thanks!!

https://hkland-my.sharepoint.com/:u:/p/zhide_goh/ERzCCBOhZaBLh5QjiStHWykBL6BPtjUW8vpVteco5nLr0g?e=yw... 

Regards,

VanCe