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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
yve214
Helper III
Helper III

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

ProductDateQty
a1/3/2022100
a1/4/2022150
a1/9/2022175
b1/10/2022200
b1/11/2022150
b1/16/2022105

 

Current Results (aggregating by default on weekend dates - Sundays):

ProductDateQty
a1/5/2022425
b1/16/2022455

 

Please my expected Results if I were to select only weekend dates (Sundays). These fixed numbers only for these dates.

ProductDateQty
a1/5/2022175
b1/16/2022105

 

Thank you for your help.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

View solution in original post

2 REPLIES 2
yve214
Helper III
Helper III

@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.

amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors