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

[BI2018No]

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?

[LivioLanzo]

Hi @BI2018No

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

[BI2018No]

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

DateTime	IsoWeek	Year	TSID	Value
01.01.2019 00	1	2019	1	1
01.01.2019 01	1	2019	1	2
01.01.2019 02	1	2019	1	3
01.01.2019 03	1	2019	1	4
01.01.2019 04	1	2019	1	5
08.01.2019 00	2	2019	1	2
08.01.2019 01	2	2019	1	3
08.01.2019 02	2	2019	1	4
08.01.2019 03	2	2019	1	5
08.01.2019 04	2	2019	1	6
01.01.2019 00	1	2019	2	10
01.01.2019 01	1	2019	2	12
01.01.2019 02	1	2019	2	14
01.01.2019 03	1	2019	2	16
01.01.2019 04	1	2019	2	18
08.01.2019 00	2	2019	2	12
08.01.2019 01	2	2019	2	14
08.01.2019 02	2	2019	2	16
08.01.2019 03	2	2019	2	18
08.01.2019 04	2	2019	2	20

[LivioLanzo · Accepted solution]

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

[BI2018No]

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!