Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Assuming I modeled my fact table to my date table through the date and fact_table date column, how can I calculate say the qty for just that specific date (week end date Sunday only) without aggregating for the entire period for instance.
Data
Product | Date | Qty |
a | 1/3/2022 | 100 |
a | 1/4/2022 | 150 |
a | 1/9/2022 | 175 |
b | 1/10/2022 | 200 |
b | 1/11/2022 | 150 |
b | 1/16/2022 | 105 |
Current Results (aggregating by default on weekend dates - Sundays):
Product | Date | Qty |
a | 1/5/2022 | 425 |
b | 1/16/2022 | 455 |
Please my expected Results if I were to select only weekend dates (Sundays). These fixed numbers only for these dates.
Product | Date | Qty |
a | 1/5/2022 | 175 |
b | 1/16/2022 | 105 |
Thank you for your help.
Solved! Go to Solution.
@yve214 , You can create a flag for that and use that in filter
new column
Weekend = if(Weekday([Date],2) =7,1,0)
new measure =
calculate(sum(Table[Qty]), filter(Table, Table[Weekend ] =1))
or , without new column
calculate(sum(Table[Qty]), filter(Table, Weekday([Date],2) =7))
@amitchandak Oh my, it was such a long day. and I dont know how I didnt think of an option like this, such a lightbulb, thank you very much.
@yve214 , You can create a flag for that and use that in filter
new column
Weekend = if(Weekday([Date],2) =7,1,0)
new measure =
calculate(sum(Table[Qty]), filter(Table, Table[Weekend ] =1))
or , without new column
calculate(sum(Table[Qty]), filter(Table, Weekday([Date],2) =7))
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
81 | |
55 | |
39 | |
20 | |
12 |