cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Set Store open/close status when selecting multiple Dates

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"

9 REPLIES 9
Anonymous
Not applicable

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

Frequent Visitor

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
Not applicable

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

Frequent Visitor

@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])

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.

Solution Specialist

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 =
SUMMARIZE ( 'Calendar', 'Calendar'[Date] ),
"Status", [Open/Close]
)
RETURN
IF (
CALCULATE ( COUNTROWS ( DailyStatus ), [Status] = 0 ) >= 1,
"Close",
"Open"
)``````
Frequent Visitor

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
Not applicable

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``````

Solution Specialist

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

Frequent Visitor

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])

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors