Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I need to set status to "0" for each store (calculated column preferred)if it satisfy the following conditions
The column in the data set are Date,Store,Time(6AM-8Pm one hour segment),Sales($) ,
Any help is appreciated.
on which line and do you have a sample file?
Hi @EV,
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,
I would be able to help lot faster with a sample file, try to creating a file with minimum data and share here
Hi @EV ,
I trierd that too then it gave the error "The expression refers to multiple columns. Multiple columns cannot be converted to scalar value"
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!
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
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |