Count working days regardless of data entry for each date

[Harry]

Hi there, 

I am new to power bi. 

Currently I have a report which contains data entries with an activity date. On the report i have a date slicer to allow the user to filter which data is viewed. 

I tried to create a measure to set target values that would use all working days within filtered date range. The problem is, the measure (and i also created a table to show dates to check) only counts/uses working days that has data entries linked with them. 

Is there anyway to create a measure that will calculate all working days (mon-fri) regardless of whether there is data stored with that acitivity date? 

I calculate 

workdays; IsWorkDay = SWITCH(WEEKDAY([Date]),1,0,7,0,1)

measure to calculate number of working days; NumberWorkingDays = SUM(DateKey[IsWorkDay])

I didn't think this should be influenced by whether or not any data is stored on that day as although my date table is linked with data table which has date column they are not the same table. Any help is appreciated!

[TomMartens]

Hey,

I created a calculated column "IsWorkday" in my calendar table using the same formula like you

IsWorkday = SWITCH('Calendar'[Day Of Week],6, 0, 7,0,1) 

I created this measure in my calendar table, I'm using ALLSELECTED() to capture the complete timeframe selected by my slicer

No of Workdays = 
	CALCULATE(
		SUM('Calendar'[IsWorkday])
		,ALLSELECTED('Calendar'[Date])
	)

I have a slicer using 'calendar'[Date]

And here is some sample output

[Harry]

Thanks for the reply tom. This now shows all dates in a table for the filtered range. But when i try to calculate a target using measure, it still only multiplies by number of working days for which there is a data entry. 

The measure is:

Daily Accum Target = (Sum('FE Target'[Weekly Target])/5)*[NumberWorkingDays]

I would like the target to be the same regardless of the data entries in the other table

[TomMartens]

Sorry, I do not understand what you mean by it still only multiplies by number of workings days ...

Which part of the multiplication is wrong did you try to put CALCULATE around your division like this CALCULATE((SUM(...)/5))

If this does not help you have to prepare sample data.

Cheers

[Harry]

Sorry for being unclear, I have a table containing data, for simplicity:   

I have a daily target value, so depending on the date filter, to calculate my target value i am trying to multiply: (daily target value * number of working days).

Rather than multiplying by number of working days in filter range, it seems to multiply by number of days which has a sales entry so in the table above it would be 3

[TomMartens]

Maybe the week has been hard, I'm missing the following information

• Which table which column are you using in your slicer that selects the timeframe
• How is the calendar table related to your table that shows Sale Value from your last post
• What is the timeframe you are selecting
• What is target value
• What is the daily target value
• What is the expected outcome, for a timeframe (2017-08-01 - 2017-08-18) 

Sorry

[Harry]

I think it may have something to do with the relationship between the two datasets. I dont fully understand the cross filter direction yet. I changed the relationship from both to single and I think it may now give the total working days regardless of the sales made