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

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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

     

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

      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