Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi, I have a dataset which has DATE, NAME, Hourbucket, Sales. I need to find out if sales was 0 for any hourbucket for a date and if true then check last year same date same hourbucket and same name to see if the sales was 0.
if both the conditions are true then "sales" else "NoSale".
Example
if Sale =0 then
check sales (Same column) a year before for the same name and hourbucket and that sales =0
then "NoSales"
else "Sales"
Any help will be appreciated.
This is the kind of dataset. Sorry the file is too big to get an exact sample
Name | hr bucket | Year | Month Name | Day | Sales |
Jon | 1 | 2016 | Nov | 12 | 0 |
Jon | 2 | 2016 | Nov | 12 | 560 |
Jon | 3 | 2016 | Nov | 12 | 647 |
Jon | 4 | 2016 | Nov | 12 | 914 |
Jon | 2 | 2016 | Nov | 13 | 996 |
Jon | 3 | 2016 | Nov | 13 | 982 |
Jon | 4 | 2016 | Nov | 13 | 664 |
Jon | 1 | 2016 | Nov | 13 | 777 |
Solved! Go to Solution.
@samHil , Try a A new column =
var _sales = sumx(filter(Table, [Name] =earlier([Name]) && [hr] =earlier([hr]) && [Month ] =earlier([Month]) && [Day] =earlier([Day])
&& [year] =earlier([year]) -1),[Sales])
return
if([sales]= 0 && (_sales+0) =0 , "NoSales" , "Sales")
@samHil , Try a A new column =
var _sales = sumx(filter(Table, [Name] =earlier([Name]) && [hr] =earlier([hr]) && [Month ] =earlier([Month]) && [Day] =earlier([Day])
&& [year] =earlier([year]) -1),[Sales])
return
if([sales]= 0 && (_sales+0) =0 , "NoSales" , "Sales")
User | Count |
---|---|
25 | |
12 | |
8 | |
7 | |
7 |
User | Count |
---|---|
25 | |
12 | |
11 | |
10 | |
6 |