Working with "Between" Dates (WeekOfYear to Day)

[clock0928]

Hi all,

I am currently attempting to adjust one of my previous measures so that I can calculate the total of 3 of my fields between said dates in my slicer visual.

My current measure that calculates for WeekOfYear is -

msrLastWeek_Total_FruitCounted = 
CALCULATE(
    SUM('Daily Statistics'[Fruit (Amount)]),
        'Calendar'[WeekOfYear] = SELECTEDVALUE('Calendar'[WeekOfYear]) - 0, ALL('Calendar')
    )

And this seems to be working just as I want it to - for this scenario my slicer has the "Year" and "WeekOfYear" and is currently a dropdown.

However, I would have thought if I used this formula with a between slicer with my "Date" field from my Calendar table, it would have calculated appropriately... Though going through the data and comparing to manual calculations, it is incorrect.

Below is the raw data that I am using to work out (alongside an example with how I work with the dates) - 

I contemplated that it could be as simple as changing the "WeekOfYear" field to "Day" from my Calendar table, however that returns a blank.

If you have any information that could help me out with this, I am all open arms for information! 😊

[jdbuchanan71]

By selecting a Year / WeekOfYear, what you are really doing is selecting a list of dates.  Because of that we can use some time inelligence funtions to get what I think you are looking for.

First, a simple Work to Complete measure:

Work to complete = SUM ( 'Work To Complete'[Fruit] )

Then the time intelligence measure for last weeks amount.

msrLastWeek_WTC = CALCULATE ( [Work to complete], DATEADD ( 'Calendar'[Date], -7, day ) )

This takes the list of dates you have selected based on your Year / WeekOfYear, shifts that list back 7 days, and returns the [Work to complete] amount for that new list of dates.

[jdbuchanan71 · Accepted solution]

Hello @clock0928 

A couple of questions.

1. Is your Calendar table a proper date table meaning it has 1 row for every day, starts on Jan 1st of the first year and runs through Dec 31st of the last year?

2. What is the granularity of your 'Daily Statistics' and how is is joined to your Calendar table?  Based on the name I am guessing you have data at the day level and it is joined to your Calendar table on the date field.

'Daily Statistics'[Date] > 'Calendar'[Date]

If those are both true, you should be able to use a simple measure of

Fruit Amount = SUM('Daily Statistics'[Fruit (Amount)])

And select multiple weeks in your "WeekOfYear" slicer to see the data for all dates between the first date and the last date.

[clock0928]

@jdbuchanan71 I feel so silly - you are 100% correct...
1. Yes, my calendar table is a proper date table
2. You are correct, just a daily entry that has the date and is jouned to my Calendar table on the date field

From that, one last question (if you have time), I am now trying to work out how to get a measure for the "Previous Work To Complete". Because this will be a varying date throughout the year, I am not certain on how I can do that with a "Day" calculation...

My measure at the moment is for a WeekOfYear (as mentioned above), which I can see how Power would be able to identify the data in that week alot easier. This is the current measure - 

msrLastWeek_WTC = 
CALCULATE(
    SUM('Work To Complete'[Fruit]),
        'Calendar'[WeekOfYear] = SELECTEDVALUE('Calendar'[WeekOfYear]) - 1, ALL('Calendar')
    )

Or am I again overcontemplating the measure and it would be simple like the previous one that you suggested... I think I would need a seperate slicer just for that field to determine the days at the end of the month... For example (with the calendar image above) the Monday and Tuesday would be considered "Previous Work to Complete", and then the "Current Work to Complete" would be the Wednesday, Thursday and Friday.