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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

aggregate group by max date - recalculate based on filters

Hi, Fellow power bi users,

IDproduct_namequantitynext_billing_datetime_stamp
1aaa1June 15, 2020May 31, 2020
1aaa2May 16, 2020May 31, 2020
2aaa1May 15, 2020May 31, 2020
2aaa2May 16, 2020May 31, 2020
1bbb1May 15, 2020May 31, 2020
1bbb2May 16, 2020May 31, 2020
2bbb1May 15, 2020May 31, 2020
2bbb2May 16, 2020May 31, 2020
3xxx1May 15, 2021May 31, 2021
3xxx2May 16, 2021May 31, 2021
4xxx1May 15, 2021May 31, 2021
4xxx2May 16, 2021May 31, 2021
3yyy1May 15, 2021May 31, 2021
3yyy2May 16, 2021May 31, 2021
4yyy1May 15, 2021May 31, 2021
4yyy3May 16, 2021May 31, 2021

 

In the above table, I want to calculate count(unique(ID)) per timestamp where max(next_billing_date) < time_stamp.  

 

 

Output 1: 

Filters: when all filters are selected

time_stamp.        total_ID_count
May 31, 20201
May 31, 20214

 

 

Output 2:

Filters:

when quantity 3 is selected

all products are selected

time_stamp.      total_ID_count
May 31, 20200
May 31, 20211

 

Output 3:

Filters:

when product aaa is selected

and all quantity is selected

time_stamp.        total_ID_count
May 31, 20201
May 31, 20210

 

 

Can you please give some pointers on how to approach this? Should I just handle it in the backend using python?

 

Update 1:

 

Looking for Group by ID, Product_name, Quantity.

If all the products are selected in the product_name then group by ID and selected Quantities. 

Similarly, If Quantities are selected in the Quantity filter then group by ID and selected Product_names. 

If all Products_name and Quantity are checked (Select All), then Just group by ID and give unique ID count. 

 

Update 2:

Can't create calculated column as it won't change with filters or slicers

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , have you tried a measure like this with timestamp

 

calculate(distinctCOUNT(Table[ID]), filter(Table, Table[next_billing_date] < Table[time_stamp]))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

@amitchandak Thank you so much for the reply. Sorry, I forgot to mention one of the conditions. I updated the question. The constraint is max(next_billing_date) < time_stamp. I need to group first and then see if all the next_billing_date within the group is < time_stamp. If so then add it to count. 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.