cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Get weekly change - first hour in week compared to previous weeks first hour

Hello!

 

I have a table with 5 columns. Time, ISOWeek, Year, TSID and Value

 

I am interessted in getting the weekly change in value from this weeks first hour compared to previous week first hour. This should work for all time series. I have manually made a extra colum from "year - week". This is linked to a slicer. When i clik week 51 2018, I want to compare hour 1 this week with hour 1 week 50 2018, and so on. How is this done in DAX?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: Get weekly change - first hour in week compared to previous weeks first hour

Hello @BI2018No

 

I have a uploaded a pbix file for you here: https://1drv.ms/u/s!AiiWkkwHZChHj1VliszSzataBKpB

 

there are a bit of changes to do to your model, especially you need to create a calendar with IsoWeek Number and Iso Week Year and create an additional dimension table for the Hours. 

You will see details in the file

 

 

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

4 REPLIES 4
Highlighted
Solution Sage
Solution Sage

Re: Get weekly change - first hour in week compared to previous weeks first hour

Hi @BI2018No

 

should be possible to do this. Are you able to post a sample of your data?

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Highlighted
Frequent Visitor

Re: Get weekly change - first hour in week compared to previous weeks first hour

 

For week 2 ID=1 I'd like to substract 1 from 2 and for ID=2 18 from 20 and so on.

 

DateTimeIsoWeekYearTSIDValue
01.01.2019 001201911
01.01.2019 011201912
01.01.2019 021201913
01.01.2019 031201914
01.01.2019 041201915
     
08.01.2019 002201912
08.01.2019 012201913
08.01.2019 022201914
08.01.2019 032201915
08.01.2019 042201916
     
01.01.2019 0012019210
01.01.2019 0112019212
01.01.2019 0212019214
01.01.2019 0312019216
01.01.2019 0412019218
     
08.01.2019 0022019212
08.01.2019 0122019214
08.01.2019 0222019216
08.01.2019 0322019218
08.01.2019 0422019220
Highlighted
Solution Sage
Solution Sage

Re: Get weekly change - first hour in week compared to previous weeks first hour

Hello @BI2018No

 

I have a uploaded a pbix file for you here: https://1drv.ms/u/s!AiiWkkwHZChHj1VliszSzataBKpB

 

there are a bit of changes to do to your model, especially you need to create a calendar with IsoWeek Number and Iso Week Year and create an additional dimension table for the Hours. 

You will see details in the file

 

 

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

Highlighted
Frequent Visitor

Re: Get weekly change - first hour in week compared to previous weeks first hour

The solution works great! Power BI works out of the box for days, months, quaters and years. Week seems to be a bit harder, at least for me. But obvious not for you! Thanks a lot! 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors