No of Working Days

[piyush0308]

I have a requirement where I need to find the working days of a store. The formula to find the working days of a store is:-

If Store is opened on or before first day of respective month then

Working Day = Total Days in a month.

If Showroom is opened in middle of respective month then

Working Day = Last Date Of month - Opening Date Of Showroom.

Please note that For current calendar month we should consider Today()-1 as Last date of month.

Showroom MasterDate MasterRequired Result Set

I Have tried below calculations but didnt work

=CALCULATE(SUM('Showroom Master'[W]),FILTER(ALL('DateMaster'),DATESBETWEEN('Date Master'[Date],FIRSTDATE('Showroom Master'[OPENINGDATE]),LASTDATE('Date Master'[Date]))),USERELATIONSHIP('Showroom Master'[OPENINGDATE],'Date Master'[Date]))

Another One

=CALCULATE(SUM('Showroom Master'[W]),USERELATIONSHIP('ShowroomMaster'[OPENINGDATE],'Date Master'[Date]),
DATESBETWEEN('Date Master'[Date],FIRSTDATE('Showroom Master'[OPENINGDATE]),LASTDATE('Date Master'[Date])))

Calculation for [W]

=(TODAY()-1)-'Showroom Master'[OPENINGDATE]

Since both Showroom and Date are master tables I cannot have directly relationship between the two.

So anybody have a solution of it

Regards,

Piyush

[Bitwize_PowerBI · Accepted solution]

Hi,

you don't need a relationship between your showroom and your date table to do what you want (and by the way, you can only use the USERELATIONSHIP function if you have at least an inactive relationship)

I have recreated your scenario in a Power BI solution where I already have a date table, and i simply added your showroom table.  I then created 3 measures and tested: it works.

i don't know your knowledge level of DAX and the 'filter context' concept, but i'll explain what happens:

if you create a pivottable or matrix with showroom and month then the Power BI engine will filter down the showroom table to one showroom only and will filter down the date table to all the dates in the choosen month.

knowing that, all you have to do is create a 'first date' measure, a 'last date' measure and an 'numberofdays' measure that calculates 'last date' minus 'first date'

my date table is called 'Datum - Document' and [Dag] is the calendar date

the first date = 
if
(
	hasonevalue(showroom[showroom]);
	if
	(
		values('showroom'[openingdate])>lastdate('Datum - Document'[Dag]);
		blank();
		if
		(
			month(values('showroom'[openingdate]))=month(lastdate('Datum - Document'[Dag]));
			values('showroom'[openingdate]);
			firstdate('Datum - Document'[Dag])
		)
	);
	blank()
)

the last date = 
if
(
	firstdate('Datum - Document'[Dag])>today();
	blank();
	if
	(
		month(today())=month(lastdate('Datum - Document'[Dag]));
		today();
		lastdate('Datum - Document'[Dag])
	)
)

numberofdays = 
if
(
	isblank([the first date])||isblank([the last date]);
	blank();
	(([the last date] - [the first date]) + 1) * 1
)

the result you want:

hope this helps.

[Anonymous]

@piyush0308

what about the below

Working Days = IF
( // Check for the current Month and year if the open date falls on the same month
MONTH(Store[OpenDate]) = MONTH(TODAY())&& YEAR(Store[OpenDate]) = YEAR(TODAY())
, DATEDIFF(Store[OpenDate],EOMONTH(Store[OpenDate],0),DAY)-1 // Minua 1 Day
,DATEDIFF(Store[OpenDate],EOMONTH(Store[OpenDate],0),DAY) //for rest of months
)