Set Store open/close status when selecting multiple Dates

[samHil]

I have store status set as open/close for each day using calculated measure to check if sales=0 "Close" or Sales >0 "Open".I checked it each day it is showing correctly. The problem am facing is that when i select week number from the calender Slicer the result is different , it shows open, What i am looking for is that if a stoe is closed for a day then the week status should show "Close". The same happen when i select multiple date which has "open" and "Close" status. The result is always "Open"
Eample:
Mon-open
Tue-open
Wed -open
Thu-Open
Fri-open
Sat-open
Sun-open
Weekly status is "Open"(Correct)
Mon-open
Tue-open
Wed -close
Thu-Open
Fri-close
Sat-close
Sun-open
Weekly status is "Open"(Wrong)-- I want the result to show "close"

[Anonymous]

How are we to solve this puzzle without knowing how you produce the statuses for different time periods? Any idea?

[samHil]

HIi @Anonymous ,

Sorry i didnt get your question. I am getting the store status using a measure which checks the sales for a day and when i select the day in calander slicer it shows correctly ,if  store has no sales it shows as "Close" but when i check for multiple days it is not showing "Close" which is my requirement( if a store has zero sales in a any of the selected day the store status should be set to "close"). I hope i made it clare.
This is the measuere i use

Open/Close =

Var __Status__Check = if([GS]=0 && [GS Prev]<>0,"Close","Open")

Return

__Status__Check

[Anonymous]

[Open/Close] =
var HasNoClosedStatus =
	CALCULATE(
		ISEMPTY( T ), // T is your fact table with statuses
		KEEPFILTERS( T[Status] = "close" )
	)
var Result =
	if( HasNoClosedStatus,
		"open",
		"closed"
	)
return
	Result

[samHil]

@Anonymous,

Status is determined by a measure.

Here is the detail
There are  3 Measures

1 Open/Close =

Var __Year__Check = if([GS]=0 && [GS Prev]<>0,"0","1")

Return

__Year__Check

2 GS = sum('pxprod'[GrossSales])

3 GS Prev = CALCULATE([GS],DATEADD(Calender_Lookup[Date].[Date],-1,YEAR))

The repport is like this

There are 3 tables

1, Calender_Lookup -- where the date is taken for slicer

2,Location_Lookup -- store is taken for the table

3,Sales -- the sales for each day is taken
Sales table is linked to Calender_lookup with date
Sales table is liked to location with store name

Hope this will give yo a better idea.

Thanks in advance

[Mohammad_Refaei]

I understand that you want to flag the week as closed if it it closed in any day.

Then you probably need to write a measure like:

Weekly Status =
VAR DailyStaus =
    ADDCOLUMNS (
        SUMMARIZE ( 'Calendar', 'Calendar'[Date] ),
        "Status", [Open/Close]
    )
RETURN
    IF (
        CALCULATE ( COUNTROWS ( DailyStatus ), [Status] = 0 ) >= 1,
        "Close",
        "Open"
    )

[samHil]

Hi @Mohammad_Refaei ,

I think this is the logic am looking for but the code is having error --"The value for 'Status' cannot be determined. Either the column doesn't exist, or there is no current row for this column."

[Anonymous]

Or like this:

[Open/Close] =
var HasNoClosedStatus =
	ISEMPTY(
		// Then, see if the result
		// is empty. If it is, all
		// days have sales and the
		// return status should be
		// "open". Otherwise, "close".
		FILTER(
			// First try to find dates
			// for which the fact table
			// is empty in the current 
			// context, meaning no sales.
			DISTINCT( T[Date] ),
			CALCULATE( ISEMPTY( T ) )
		)
	)
var Result =
	if( HasNoClosedStatus,
		"open",
		"closed"
	)
return
	Result

[Mohammad_Refaei]

It will help if you share your model table structure and measures

[samHil]

Hi @Mohammad_Refaei ,
Here is the detail
There are  3 Measures

1 Open/Close =

Var __Year__Check = if([GS]=0 && [GS Prev]<>0,"0","1")

Return

__Year__Check

2 GS = sum('pxprod'[GrossSales])

3 GS Prev = CALCULATE([GS],DATEADD(Calender_Lookup[Date].[Date],-1,YEAR))

The repport is like this

There are 3 tables

1, Calender_Lookup -- where the date is taken for slicer

2,Location_Lookup -- store is taken for the table

3,Sales -- the sales for each day is taken
Sales table is linked to Calender_lookup with date
Sales table is liked to location with store name

Hope this will give yo a better idea.

Thanks in advance