Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
What is the easiest way of comparing values for datetime periods with the same datetime a year earlier?
For example these are the column headers for a table . Each ProductID (of which there are 39) has a value for everyhour of everyday.
I want to compare this hour with the same hour a year earlier.
I seem to run into two problems - the built in functions like SAMEPERIODLASTYEAR only work with dates, not datetimes.
Also because I have 39 products IDs, each datetime appears 39 times (because there is one entry for each product ID) and this causes problems as using DateAdd,-1, Year seems to only work when the date is unique.
I have a DateTable and a DateTime table but I can't work out how this helps given the hourly granularity.
The users needs to be able to switch easily between product IDs and compare one or more on the same visual
thanks
Table
DateTime ProductID Value
Solved! Go to Solution.
hi, @Anonymous
After my test, you could try this way like below:
Step1:
Add a date column and a hour column for datetime column
Date = DATE(YEAR(Table1[Datetime]),MONTH(Table1[Datetime]),DAY(Table1[Datetime]))
Hour = HOUR(Table1[Datetime])
Step2:
Use CONTAINS to create a formula instead of SAMEPERIODLASTYEAR
Measure 4 = CALCULATE ( [Measure], FILTER ( ALLSELECTED(Table1), CONTAINS ( Table1, Table1[Date], DATE ( YEAR ( Table1[Datetime] ) + 1, MONTH ( Table1[Datetime] ), DAY ( Table1[Datetime] ) ),Table1[Hour],HOUR(Table1[Datetime]) ) ) )
Result:
and here is pbix file, please try it.
Best Regards,
Lin
Hi @Anonymous
You could try building "manually" the date you want by:
a) Extracting date and time from the current DateTime you need to compare
b) Building date -1 year
c) Adding the time extracted in a) to the result of b)
and then use the DateTime built in c) to extract the value of interest in your table
Can you share a sample of your data?
Hi
Thanks - I've attached a link to a sample file in google drive
Just so you know what I am attempting to do (but I don't expect you to do all the work for me!):
I'm interested in being able
1) compare the value for the same date and time period from last year to this year (so 3am on 21 January 2019 with 3am on 21 January 2018)
2) create a measure showing the year on year change - this ideally should be aggregateable by date hiearchy so that people can see the % change by day/month/year
3) users will want to be able to easily compare countries to see which had the largest year on year changes
hi, @Anonymous
After my test, you could try this way like below:
Step1:
Add a date column and a hour column for datetime column
Date = DATE(YEAR(Table1[Datetime]),MONTH(Table1[Datetime]),DAY(Table1[Datetime]))
Hour = HOUR(Table1[Datetime])
Step2:
Use CONTAINS to create a formula instead of SAMEPERIODLASTYEAR
Measure 4 = CALCULATE ( [Measure], FILTER ( ALLSELECTED(Table1), CONTAINS ( Table1, Table1[Date], DATE ( YEAR ( Table1[Datetime] ) + 1, MONTH ( Table1[Datetime] ), DAY ( Table1[Datetime] ) ),Table1[Hour],HOUR(Table1[Datetime]) ) ) )
Result:
and here is pbix file, please try it.
Best Regards,
Lin
Thanks Lin!
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |