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.
Store Data
Date Table
Required Result
=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 Solution
=CALCULATE(SUM('Showroom Master'[W]),USERELATIONSHIP('ShowroomMaster'[OPENINGDATE],'Date Master'[Date]),
DATESBETWEEN('Date Master'[Date],FIRSTDATE('Showroom Master'[OPENINGDATE]),LASTDATE('Date Master'[Date])))
Calculation [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
