Forum Discussion
How to get fixed values without aggregating
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.
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))
2 Replies
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.
