Get last week data up until current time

DAX DAX Calculations
1

Hi everyone,

I am looking to create a visual which displays data from the previous week up until the current time.

For example, if today is Wednesday at 11am, I would like to see my data from last week for
Monday full day, Tuesday full day, Wednesday until 11am.

Currently, the previous week formula I am using looks at the full day for Wednesday, therefore it is not a like for like comparison (we are measuring footfall).

How the DAX is set up at the moment:
The current week is a simple SUM() calculation
The previous week is CALCULATE( [Current Week], DATEADD ( ā€˜Dateā€™[Date], -7, DAY )

Many thanks in advance!
Chloe
stripped down.zip (217.6 KB)

1 Like
2

Hi @Clolawra,
Thank you very much for posting your query in the forum.

When opening the pbix file I get this error:

image

Regards,

3

@Clolawra

See attached file for new calculation.
pfm_stripped Solution Dashboarding Meesters.pbix (138.1 KB)

I am not sure if I understand you correctly, but i thought you would like to make a week to date calculation, so i googled it and landed on this blog ; Week to Date Calculation in Power BI with DAX - RADACAD

I implemented it immediately on your case. Hopefully itā€™s the solution.

If something is unclear, read the blog or feel free to ask more questions :slight_smile:

Kind regards,
Pim Meesters

4

Hi @DashboardingMeesters

Thanks for taking a look into this for me.

I believe the week to date calculation is almost what I am after, however I canā€™t tell whether this format also looks at the current time in the day, rather than just the date?

So essentially, I would be after ā€œweek to current date and timeā€, so I can see the previous week, up to the current time.

Hope this makes sense - and thanks again for your help!

Chloe

5

@Clolawra
You could use the following to get the right numbers

CurrentDateTime = Now()
LastWeekDateTime = [Measure]-7

Then you can get yor data by f
filter on the datetime >=LastWeekDateTime
&& datetime <=CurrentDateTime

Even if you donty like this as a solution it will help teest Week to date querys.

Hope that helps
Ells

image
image1920Ɨ1039 71.1 KB

6

Hi @ells,

I have just tried to give that a go however I get the following error - I think because I am using a measure in the filter?

image
image1622Ɨ124 13.9 KB

Do you have any idea if there is a better way for me to run the filter?

Thank you,
Chloe

7

@Clolawra
In the Measure you could go
Total FootfallUntil current time =
VAR CurrentTime = NOW()
VAR CurrentTimeLastWeek = CurrentTime - 7
VAR Result = CLACULATE(
[Total Footfall],
count_measurement_feed[date_from] +count_measurement_feed[time] <= CurrentTimeLastWeek
)
RETURN
Result

I suspect the issue maybe where you are adding the date and time column
I would suggest you check what count_measurement_feed[date_from] +count_measurement_feed[time] gives you by adding a calculated column temporarily.

Regards
Ells

)

8

Hi @Clolawra,
Since I couldnā€™t open your pbix file, I built a solution from scratch from your data.

I have created a dimension table of Dates, Time and the following measure:

Total count_fw LastWeekCurrentTime = 
VAR _Today =
TODAY() 
VAR _CurrentDayofWeek = MAXX(
    FILTER(
        ALL( Dim_Dates ),
        Dim_Dates[Date] = _Today
    ),
    Dim_Dates[DayofWeek]
) 
VAR _Now = NOW() 
VAR _CurrentTime = TIME( HOUR( _Now ), MINUTE( _Now ), 0 ) 
VAR _LastweekDaysCompleted = CALCULATE(
    [Total count_fw],
    FILTER(
        ALLSELECTED( Dim_Dates ),
        Dim_Dates[WeekOffset] = -1 &&
        Dim_Dates[DayofWeek] < _CurrentDayofWeek
    )
) 
VAR _LastweekLastDay = CALCULATE(
    [Total count_fw],
    FILTER(
        ALLSELECTED( Dim_Dates ),
        Dim_Dates[WeekOffset] = -1 &&
        Dim_Dates[DayofWeek] = _CurrentDayofWeek
    ),
    FILTER(
        ALL( Dim_Times ),
        Dim_Times[Time] <= _CurrentTime
    )
) 
VAR _Result = _LastweekDaysCompleted + _LastweekLastDay 

RETURN
_Result

I hope it can be of help to you.
Regards

pfm_stripped_JAFP.pbix (534.3 KB)

9

Hi @ells and @jafernandezpuga - thanks so much for this!
I wonā€™t get chance to look into this now until Monday so please leave it with me and I will get back to you as soon as I can :slight_smile:

1 Like
10

Hi @ells and @jafernandezpuga

I have tried both these solutions and it appears to be working as expected!

Thanks so much for taking your time to look into this for me, Iā€™ve noted the formulas down for future reference for when I forget in a few months time :rofl:

Chloe

1 Like