Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
Os is there a way that I can create a new table based on the 2 tables I have where the budget gets split in the new table based on the number of Mondays , Tuesdays , Wednesdays in a month and place the value against each date for each state and catergory?
Any help is greatly appreciated.
Hi , @obiee1
Do you have resolved it? For now, there is no content of description in the thread.
If you still need help, please share more details to us.
Best Regards,
Community Support Team _ Eason
Hi , @obiee1
The column "week " seem to be wrong in your "Date Table" .
The weekbum should be 27 rather than 26
It is recommended to create a specific date column that meets the requirements in budget table and a date column in DateTable. So that you can select related date columns to create a relationship between two tables.
About how to construct the date columns in two tables, please check the samlple file.
Then you can create measure as below:
Measure =
SELECTEDVALUE('Budget Table'[Amount])/SELECTEDVALUE('Calendar Table'[num_of_weekday_in_same_month])
The result will show as below:
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |