Creating month-to-date from weekly summarized and daily data

[Anonymous]

I'm sure this has been solved somewhere before but I couldn't find it.

I have a dataset of "week-to-date" timeclock hours given to me daily from our time and attendance system.

I also have a dataset of daily earned hours from our ERP.

For demo purposes let's say I have one department with one person in it and everything matches perfectly (I wish).

Let's say it's also currently 4/9/22 and I'm trying to pull month-to-date data.

Time and attendance data:

Date	DayOfWeek	T&A Hours
3/28/2022	Monday	40
3/29/2022	Tuesday	80
3/30/2022	Wednesday	120
3/31/2022	Thursday	160
4/1/2022	Friday	200
4/2/2022	Saturday	210
4/3/2022	Sunday	210
4/4/2022	Monday	40
4/5/2022	Tuesday	60
4/6/2022	Wednesday	100
4/7/2022	Thursday	140
4/8/2022	Friday	180

ERP Data

Date	DayOfWeek	ERP Hours
3/28/2022	Monday	40
3/29/2022	Tuesday	40
3/30/2022	Wednesday	40
3/31/2022	Thursday	40
4/1/2022	Friday	40
4/2/2022	Saturday	10
4/3/2022	Sunday	0
4/4/2022	Monday	40
4/5/2022	Tuesday	20
4/6/2022	Wednesday	40
4/7/2022	Thursday	40
4/8/2022	Friday	40

I don't want to try to split the T&A data between 4/1 and 3/31 (the system is messy and I only fully trust a weekly summary). I want to summarize the data by: (Current Month) OR (last month and ended in a Sunday this month).

So the example data would give me

T&A = 210+180 = 390

ERP = sum of 3/28-4/8 = 390

Creating a measure for T&A was easy by summing where the date was in the month and either yesterday or Sunday.

However, I haven't been able to figure out a good way to make PowerBI understand the same for the ERP data as part of a measure.

I've considered importing the table a second time and creating a weekly summary as part of that import logic but I didn't really like the idea.

Any thoughts/suggestions? I can also provide more detail as-needed.

Thank you 🙂

[Anonymous]

I hate when the answers are that obvious in hindsight 😆 Thank you!!

[johnt75 · Accepted solution]

You could add a column to your date table like

Week end month = 
var nextSunday = 'Date'[Date] - WEEKDAY( 'Date'[Date], 2 ) + 7
return MONTH(nextSunday)

then in a filter you can check for either 'Date'[Month] is the current month or 'Date'[Week end month] is the current month