cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
elegreen
Helper I
Helper I

Same DateTime period a year earlier

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

1 ACCEPTED SOLUTION

hi, @elegreen

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:

4.JPG

and here is pbix file, please try it.

 

Best Regards,

Lin

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

Hi @elegreen

 

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

 

Hopefully a working lin

hi, @elegreen

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:

4.JPG

and here is pbix file, please try it.

 

Best Regards,

Lin

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks Lin!

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors