Latest Enterprise DNA Initiatives


Compare weeks within a month for equal number of weeks in previous month

What I’m working on is the weekly hours analysis and the issue I’m having is trying to create a measure to compare this Months hours to the previous months hours, but the issue is that as it stands this month has only 3 full weeks hours of data, and last month has 4 weeks and I need a like with like comparison, therefore to compare the first 3 weeks of last month to the 3 weeks in this month.

The way I originally tried to achieve this was to divide last months weeks by a count of the weeks in that month (4) and multiply by the count of weeks in the current month (3), but that only gave me an average of 3 weeks of last month and not an exact like with like to the first 3 weeks of this month.

Might you know of any measure to sum the number of weeks in the prior month to match the number of weeks in the current month ?
I want to compare like with like, apples to apples.

@shane.cashman ,

Here’s a video series I did recently on comparing the first N business days of each month to the same N business days of the previous month. I think this provides the general approach that could be taken to do the sort of apples to apples comparison you are looking for. The first video focuses on primaryily a Power Query-based solution, while the next one is entirely DAX-driven.

I hope you find these helpful.

  • Brian
2 Likes

Thanks for working on a solution on this post @BrianJ.

Hello @shane.cashman, welcome to the forum :slight_smile:

Did the response above solve your query?

Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’

Hi @shane.cashman, we’ve noticed that no response has been received from you since a few days ago.

We just want to check if you still need further help with this post?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi @shane.cashman, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.

Thank you for the link, @BrianJ ! Seem to be useful in my work too.

@StaceyL,

That’s great to hear. When I first created these two videos, I had the sense that this pattern would find a lot of different practical applications.

  • Brian