Store status based on conditions

[samHil]

I need to set status to "0" for each store (calculated column preferred)if it satisfy the following conditions

• Store has Zero sales and corresponding day last year is not Zero sales
• Store has first 2 hours of opening(6am&7am) has zero sales and corresponding time/day last year is not Zero sales
• Zero Sales for a continuous 2hr period from 9am to 3pm ( that is out of 9,10,11,12,13,14,15 hours segments check if it had Zero Sales for continuous 2 hours) and corresponding time/day last year is not Zero sales

The column in the data set are Date,Store,Time(6AM-8Pm one hour segment),Sales($) ,

Any help is appreciated.

[Anonymous]

on which line and do you have a sample file?

[samHil]

Hi @Anonymous,

I think it is the last line If() condition am not sure though

The data file is very big. i have extracted a sample data.

Date	Hour	Sales	Store
1/01/2020 0:00	6	47.1	BB
1/01/2020 0:00	7	40.9	BB
1/01/2020 0:00	8	309.05	BB
1/01/2020 0:00	9	285.6	BB
1/01/2020 0:00	10	518.4	BB
1/01/2020 0:00	11	725.45	BB
1/01/2020 0:00	12	485.8	BB
1/01/2020 0:00	13	463.2	BB
1/01/2020 0:00	14	399.65	BB
1/01/2020 0:00	15	342.85	BB
1/01/2020 0:00	16	218.3	BB
1/01/2020 0:00	17	1214.95	BB
2/01/2020 0:00	6	97.55	BB
2/01/2020 0:00	7	78.45	BB
2/01/2020 0:00	8	264.96	BB
2/01/2020 0:00	9	248.8	BB
2/01/2020 0:00	10	419.85	BB
2/01/2020 0:00	11	647.4	BB
2/01/2020 0:00	12	550.95	BB
2/01/2020 0:00	13	430.75	BB
2/01/2020 0:00	14	253.15	BB
2/01/2020 0:00	15	299.4	BB
2/01/2020 0:00	16	194.5	BB
2/01/2020 0:00	17	1148.5	BB
3/01/2020 0:00	6	75.4	BB
3/01/2020 0:00	7	115.65	BB
3/01/2020 0:00	8	218.1	BB
3/01/2020 0:00	9	338.1	BB
3/01/2020 0:00	10	365.25	BB
3/01/2020 0:00	11	625.2	BB
3/01/2020 0:00	12	924.65	BB
3/01/2020 0:00	13	530.54	BB
3/01/2020 0:00	14	354.95	BB
3/01/2020 0:00	15	266.05	BB
3/01/2020 0:00	16	218.75	BB
3/01/2020 0:00	17	1644.85	BB
4/01/2020 0:00	6	64.05	BB
4/01/2020 0:00	7	169.05	BB
4/01/2020 0:00	8	292.4	BB
4/01/2020 0:00	9	310.15	BB
4/01/2020 0:00	10	463.8	BB
4/01/2020 0:00	11	619.3	BB
4/01/2020 0:00	12	823.4	BB
4/01/2020 0:00	13	522.25	BB
4/01/2020 0:00	14	238.85	BB
4/01/2020 0:00	15	325.3	BB

Thank s in Advance,

[Anonymous]

I would be able to help lot faster with a sample file, try to creating a file with minimum data and share here

[samHil]

Hi @Anonymous ,

I trierd that too then it gave the error "The expression refers to multiple columns. Multiple columns cannot be converted to scalar value"

[Tanushree_Kapse]

Hi @samHil ,

Use the below dax:
Column=  
Var a= CALCULATE(SUM(Sales))
Var b= CALCULATE(b, SAMEPERIODLASTYEAR(Date))
Var c= CALCULATE(a, FILTER( Table, Table(Time)= "6 am" || Table(Time)= "7 am"))
Var d= CALCULATE(c, SAMEPERIODLASTYEAR(Date))
Var e= CALCULATE(COUNTROWS(FILTER(Table, Table[Sales]= 0)), FILTER(Table, Table(Time)>= 9am || Table(Time)>= 3pm ))
Var f= CALCULATE(e, SAMEPERIODLASTYEAR(Date))

Return IF(AND(AND(AND(AND(AND(a= 0, b >0), c=0),d>0),e>2),f=0), 0, BLANK())

I hope this helps.
Kudos are always appreciated!

[samHil]

Hi @Tanushree_Kapse 

Thank you for the reply. I executed the code and got an error.
Var b= CALCULATE(b, SAMEPERIODLASTYEAR(Date)) The "b" inside the Calculate function is not valid.
I think we cannot declare the variable and use it at the same time.
Thank you,
SAM

[Anonymous]

yeah, it must have been a typo, since you are looking at sales last year , replace b with a

Var b= CALCULATE(a, SAMEPERIODLASTYEAR(Date))