Forum Discussion

samHil's avatar
samHil
Frequent Visitor
5 years ago

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...
samHil's avatar
samHil
Frequent Visitor
5 years ago

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

 

Mohammad_Refaei's avatar
Mohammad_Refaei
Icon for Solution Specialist rankSolution Specialist
5 years ago

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's avatar
    samHil
    Frequent Visitor
    5 years ago

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

    • Mohammad_Refaei's avatar
      Mohammad_Refaei
      Icon for Solution Specialist rankSolution Specialist
      5 years ago

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

      • samHil's avatar
        samHil
        Frequent Visitor
        5 years ago

        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
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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