Relationship between static values and date values (relationships between tables may be needed)

[OuluChris]

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

[v-robertq-msft · Accepted solution]

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:

1. Create a calender table to get the data across 5 years:

Date = CALENDAR(DATE(2021,1,1),DATE(2025,12,31))

2. 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:

3. Create a measure:

Sum of working days =

SUMX(ALLSELECTED('Date'),[Is Working Day])

4. 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.

[OuluChris]

Thanks 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, @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.

[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?

[PhilipTreacy]

Hi Chris,

Download sample PBIX file.

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