Forum Discussion
Using Monthly Data for Weekly Calculations
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.
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
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.⚡
2 Replies
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
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.⚡
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?