Week comparison until same hour of current day

[Anonymous]

Hi all,

I have build a dataset with a standard date, time and fact table. In my fact table I store data on hourly level. My date and time table are very standard, the hour table only has 24 rows of information (for each hour 1 row). Both the date and time table have a relationship to my fact table. In the fact table I have stored date and time as two seperate columns. There is also a basis for all the calculations; current week, previous week, previous year etc. 

But now comes the difficult part. The dataset refreshes every hour the data during a day. That means that currently the latest refresh has been done at 25/07/2022 16:00. This means that the dataset already contains data from up to this date and time. 

But when comparing the totals for the date or time of today's date vs last week same date, the compared date calculation is taking the full day for the comparison instead to compare it to also the latest time of the current date/ latest refresh data. 

The same applies when comparing a full week when you are in the middle of the week. Let's take wednesday 15:00 for example. Compare the current week (mon and tue full day, wed until 15:00) vs previous week (mo, tue, wed). The previous week is taking all 3 the full days in the comparison. But I want to have wednesday to only count until 15:00 as well.

Tried looking for a solution but could not find anything. My current week is just a regular SUM() calculation, the previous week is : CALCULATE( [Current Week], DATEADD ( 'Date'[Date], -7, DAY )

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create measure.

current week =
var _now=NOW()
VAR _TIME=DATE(
    YEAR(_now),MONTH(_now),DAY(_now)) + TIME(HOUR(_now),0,0)
var _currentfistdaynow=_now - WEEKDAY(_now,3)
var _currentfirst00=DATE(YEAR(_currentfistdaynow),MONTH(_currentfistdaynow),DAY(_currentfistdaynow))+ TIME(0,0,0)
var _lastfirstday00=
DATE(YEAR(_currentfistdaynow),MONTH(_currentfistdaynow),DAY(_currentfistdaynow)-7)+ TIME(0,0,0)
var _lastnowday=DATE(YEAR(_now),MONTH(_now),DAY(_now)-7)+ TIME(HOUR(_now),0,0)
return
CALCULATE(
    SUM('Table'[Amount]),FILTER(
        ALL('Table'),'Table'[Date] >= _currentfirst00&&'Table'[Date]<=_TIME))

previous week =
var _now=NOW()
VAR _TIME=DATE(
    YEAR(_now),MONTH(_now),DAY(_now)) + TIME(HOUR(_now),0,0)
var _currentfistdaynow=_now - WEEKDAY(_now,3)
var _currentfirst00=DATE(YEAR(_currentfistdaynow),MONTH(_currentfistdaynow),DAY(_currentfistdaynow))+ TIME(0,0,0)
var _lastfirstday00=
DATE(YEAR(_currentfistdaynow),MONTH(_currentfistdaynow),DAY(_currentfistdaynow)-7)+ TIME(0,0,0)
var _lastnowday=DATE(YEAR(_now),MONTH(_now),DAY(_now)-7)+ TIME(HOUR(_now),0,0)
return
CALCULATE(
    SUM('Table'[Amount]),FILTER(
        ALL('Table'),'Table'[Date]>= _lastfirstday00&&'Table'[Date]<=_lastnowday))

2. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly