topic Re: Store status based on conditions in DAX Commands and Tips

Store status based on conditions

samHil — Tue, 13 Jul 2021 13:17:00 GMT

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.

Re: Store status based on conditions

Tanushree_Kapse — Tue, 13 Jul 2021 14:10:43 GMT

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!

Re: Store status based on conditions

samHil — Wed, 14 Jul 2021 00:19:48 GMT

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

Re: Store status based on conditions

Anonymous — Wed, 14 Jul 2021 00:42:18 GMT

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

Re: Store status based on conditions

samHil — Wed, 14 Jul 2021 00:45:50 GMT

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"

Re: Store status based on conditions

Anonymous — Wed, 14 Jul 2021 00:49:21 GMT

on which line and do you have a sample file?

Re: Store status based on conditions

samHil — Wed, 14 Jul 2021 00:58:38 GMT

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,

Re: Store status based on conditions

Anonymous — Wed, 14 Jul 2021 01:11:32 GMT

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