Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Everyone,
This is the first time I'm asking a question and would like to get some help a situation that I can't seem to get out of. I have a date table, and a budget table. The budget table looks something like this
Budget
State | Category | Month | Day | Amount |
XXX | abc | July | Wednesday | $500 |
XXX | abc | Aug | Monday | $600 |
XXX | abc | July | Thursday | $1000 |
XXX | xyz | July | Monday | $100 |
ZZZ | abc | July | Monday | $700 |
Date table
Date Month Year Day Name Day Week Month Name
01/07/2020 2020 Wednesday 1 26 July
02/07/2020 2020 Thursday 2 26 July
03/07/2020 2020 Friday 3 26 July
.......
What I'm trying to do is to split the budget number split across days but it should be able to take into account the number of Mondays in month and in a week to calculate to calculate target on daily basis
so If the selected day is just 01/07/2020 for category abc for state XXX then result should be $100 as there are 5 Wednesdays in the month of July
If the date selection is 01/07/2020 to 02/07/2020 for category abc for state XXX then result should be $300.
Not sure if a measure or a new table is the correct choice.
Is there a way I can do a cross join the Budget and Date table to create a new table, and split the budget across each date ? so if the budget is $500 for 5 wednesdays in the month of July for state XXX and category abc, it will place $100 against each Wednesday for the month of July, and for Mondays in July for category abc for state ZZZ it will say ($700/4) = $175 for each date.
Any help is greatly appreciated.
Solved! Go to Solution.
I didn't have time to try this out but I think this approach will work, doing it all in the query editor.
1. Start with your budget table
2. Merge it with your Date table (created in M obviously). Make the joins on the Month and Day columns.
3. Before you expand it, add a custom column to count the # of rows in the merged Date table (e.g., # of Wednesdays)
4. Add another column to divide your total amount by the count in #3
5. Expand your Date table to get your result with the Date you need and the divided amount
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
I didn't have time to try this out but I think this approach will work, doing it all in the query editor.
1. Start with your budget table
2. Merge it with your Date table (created in M obviously). Make the joins on the Month and Day columns.
3. Before you expand it, add a custom column to count the # of rows in the merged Date table (e.g., # of Wednesdays)
4. Add another column to divide your total amount by the count in #3
5. Expand your Date table to get your result with the Date you need and the divided amount
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi Mahoneypat,
Thank you for the reply, I think this could be a solution. I struggled with pont 3 of yours though.
If i'm adding it before expanding then would I do it before merge also? or have I not undstood it correctly?
Regards
Hi @obiee1
Do you still have issues with this?
If you've fixed the issue on your own please kindly share your solution. If any of the above posts helped, please kindly mark it as a solution to help others find it more quickly. Thanks!
Cheers,
Sturla
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |