Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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, @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:

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 @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?

 

Anonymous
Not applicable

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, @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:

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.
Anonymous
Not applicable

Thanks Lin!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.