Forum Discussion
Helper IRelationship between static values and date values (relationships between tables may be needed)
Hi,
I have a table that looks like this:
| Name | Availability | Utilisation Target | Cost Rate |
| Chris | 1 | 0.90 | 100 |
| John | 0.5 | 0.90 | 125 |
| Peter | 0.8 | 0.70 | 85 |
Which when multiplied together can give me a daily target rate.
I also have a calendar table that calculates the number of working days in a month. However, there is no relationship between these and neither can there be.
I want to multiply the daily target rate by the number of working days in a month but writing this as a measure gives the warning "Relationships between tables may be needed". Is there a way to write the measure so that this doesn't happen (which would be my preferred route) or, if not, is there a way to suppress this warning?
Thanks,
Chris
Hi, @
According to your description, I think you can use measure and slicer in Power BI to achieve your requirement, you can try this method:
- Create a calender table to get the data across 5 years:
Date = CALENDAR(DATE(2021,1,1),DATE(2025,12,31))- Create these calculated columns in the date table:
Month = FORMAT([Date],"mmm")Month-Year = [Month]&"-"&RIGHT(YEAR([Date]),2)Month-Year1 = YEAR([Date])&FORMAT([Date],"mm")Is Working Day = IF(WEEKDAY([Date],2)>5,0,1)Then sort the column [Month-Year] like this to make it ordered in the slicer:
- Create a measure:
Sum of working days = SUMX(ALLSELECTED('Date'),[Is Working Day])- Create a slicer to place [Month-Year] and a card chart to place the measure:
And you can get what you want.
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- PhilipTreacy
Super User
Hi Chris,
How are you determing what month it is, and therefore feeding that into your measure?
If you use TODAY for example, you could work out the working days in this month like this (mulitply this by your daily rate to get your desired answer)
Monthly Target Rate = CALCULATE(COUNTROWS('DateTable'), FILTER('DateTable', MONTH('DateTable'[Date]) = MONTH(TODAY()) && YEAR('DateTable'[Date]) = YEAR(TODAY()) && 'DateTable'[IsWorkingDay] = TRUE))This requires a date table that has a column to indicate if a day is a working day or not. This requires a list of holidays to be created for the Date Table to use.
The measure above gives 23 working days in Mar 2021 which is correct.
Check my attached PBIX which has a Date Table showing working days.
regards
Phil
- Ashish_Mathur
Hi,
I cannot understand your question. If there is no date/month in the table that you have shared, how will one know how many days do we have to multiply by?
- v-robertq-msft
Community Support
Hi, OuluChris
According to your description and sample data, your data table doesn’t have any date column to link to the ‘Date’ table. So what’s the expected relationship between your data table and the calendar table?
If you want to get the number of work days within each month, PhilipTreacy has posted the solution. If you want to suppress this warning, I think give a date column to your data table is very necessary.
You can post your sample pbix file or some sample data(without sensitive data) and your expected result so that we can help you in advance.
Thank you very much!
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
OuluChrisThanks for the replies everybody. I see now I wasn't as clear as I should have been in describing the problem.
So I have the table I showed previously:
Name Availability Utilisation Target Cost Rate Chris 1 0.90 100 John 0.5 0.90 125 Peter 0.8 0.70 85 And I also have a date table that looks like this:
Date Year Month Number Month-Year Month-Year Sort Is Working Day? 01/01/2021 2021 1 Jan-21 2021001 0 02/01/2021 2021 1 Jan-21 2021001 0 03/01/2021 2021 1 Jan-21 2021001 0 04/01/2021 2021 1 Jan-21 2021001 1 FORMULA At the bottom of this table (which includes five years worth of dates) is the formula:
Available Working Days:=SUM([Is Working Day?])
This then allows me to figure out the total number of working days in a pivot table by selecting the Month-Year from the filter (or using the month-year in other ways but it's the filter I'm mostly interested in).
This all works but generates the warning message I referred to previously.
I hope this makes it a bit clearer.
Thanks a lot.
Chris
- v-robertq-msft
Hi, @
According to your description, I think you can use measure and slicer in Power BI to achieve your requirement, you can try this method:
- Create a calender table to get the data across 5 years:
Date = CALENDAR(DATE(2021,1,1),DATE(2025,12,31))- Create these calculated columns in the date table:
Month = FORMAT([Date],"mmm")Month-Year = [Month]&"-"&RIGHT(YEAR([Date]),2)Month-Year1 = YEAR([Date])&FORMAT([Date],"mm")Is Working Day = IF(WEEKDAY([Date],2)>5,0,1)Then sort the column [Month-Year] like this to make it ordered in the slicer:
- Create a measure:
Sum of working days = SUMX(ALLSELECTED('Date'),[Is Working Day])- Create a slicer to place [Month-Year] and a card chart to place the measure:
And you can get what you want.
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
