Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
samHil
Frequent Visitor

Store status based on conditions

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.

7 REPLIES 7
Anonymous
Not applicable

on which line and do you have a sample file?

 

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.

DateHourSalesStore
1/01/2020 0:00647.1BB
1/01/2020 0:00740.9BB
1/01/2020 0:008309.05BB
1/01/2020 0:009285.6BB
1/01/2020 0:0010518.4BB
1/01/2020 0:0011725.45BB
1/01/2020 0:0012485.8BB
1/01/2020 0:0013463.2BB
1/01/2020 0:0014399.65BB
1/01/2020 0:0015342.85BB
1/01/2020 0:0016218.3BB
1/01/2020 0:00171214.95BB
2/01/2020 0:00697.55BB
2/01/2020 0:00778.45BB
2/01/2020 0:008264.96BB
2/01/2020 0:009248.8BB
2/01/2020 0:0010419.85BB
2/01/2020 0:0011647.4BB
2/01/2020 0:0012550.95BB
2/01/2020 0:0013430.75BB
2/01/2020 0:0014253.15BB
2/01/2020 0:0015299.4BB
2/01/2020 0:0016194.5BB
2/01/2020 0:00171148.5BB
3/01/2020 0:00675.4BB
3/01/2020 0:007115.65BB
3/01/2020 0:008218.1BB
3/01/2020 0:009338.1BB
3/01/2020 0:0010365.25BB
3/01/2020 0:0011625.2BB
3/01/2020 0:0012924.65BB
3/01/2020 0:0013530.54BB
3/01/2020 0:0014354.95BB
3/01/2020 0:0015266.05BB
3/01/2020 0:0016218.75BB
3/01/2020 0:00171644.85BB
4/01/2020 0:00664.05BB
4/01/2020 0:007169.05BB
4/01/2020 0:008292.4BB
4/01/2020 0:009310.15BB
4/01/2020 0:0010463.8BB
4/01/2020 0:0011619.3BB
4/01/2020 0:0012823.4BB
4/01/2020 0:0013522.25BB
4/01/2020 0:0014238.85BB
4/01/2020 0:0015325.3BB

 

Thank s in Advance,

Anonymous
Not applicable

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

samHil
Frequent Visitor

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
Impactful Individual
Impactful Individual

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!

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
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.