Using Monthly Data for Weekly Calculations

[Anonymous]

Hello,

I'm having difficulty figuring out how to solve my data shortage problem. I have a data set that is calculating the number of new accounts per employees, but my new accounts data is weekly and my employee data is monthly. Naturally , power Bi does the following:

I'm not sure how to make Power BI read the employee information based on the month and not the week. Please note that the dates are linked to a single master calendar table, but of course Power BI doesn't do weekly breakdowns. Any help is greatly appreciated. 

[parry2k · Accepted solution]

@Anonymous here is the output and measure to calculate this

Measures

Total Accounts = SUM ( Emp[Accounts] )

Emp Count = 
VAR m = MAX ( 'Date'[Month] )
RETURN 
CALCULATE ( 
    SUM ( Emp[Employee Count] ),
    ALL ( 'Date' ), 
    'Date'[Month] = m 
) * 
DIVIDE ( [Total Accounts], [Total Accounts] ) 

Accts/Emp = DIVIDE ( [Total Accounts], Emp[Emp Count] ) 

Follow this to add date dimension if you don't have one

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

@parry2k This solution is so so close! For future reference, I also had to include a variable for the year because my data is spread over multiple years. But there is a weird problem that's also occurring. 

It seems like the employee count is randomly taking in the previous month's data. In the example in the picture, the employee count for 2/2/2019 should be 487 not 756. I cannot find a pattern in my data for why this is occurring but it appears throughout my whole dataset, any ideas?