Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
samHil
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?

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

 

@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
samHil_0-1627213342048.png

 

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

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

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

 

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

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
samHil_0-1627128131436.png

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.