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