Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Solved! Go to 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]
)
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]
)
Thank you Xue Ding,
The examples are very helpful!
Regards,
VanCe
use something like:
Measure =
DIVIDE (
[TotalWaterConsumed],
CALCULATE ( [TotalWaterConsumed], Dates[Year] = 2008 )
)
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
Hi,
Can i use the quick measure % difference and then edit, basically i just add in the calculate in the filter part:
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
Why does my link gets deleted away ? ☹️
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.
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
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!!
Regards,
VanCe
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
83 | |
64 | |
61 | |
55 |
User | Count |
---|---|
179 | |
108 | |
105 | |
71 | |
70 |