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

 

[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