cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
meski Frequent Visitor
Frequent Visitor

Aggregating measure based on dates

Hi

 

I am working with invoice level data but am trying to create a measure which applies conditions at the aggregated customer level within a period. Example below shows invoice $ by customer and from this I am trying to count how many customers are within terms (ie assessing all the outstanding invoices for the period and determining that total overdue =0 at a customer level - refer boxed column).  My attempts so far haven't been able to get past the row context so each invoice is being evaulated but am sure it's more straightforward than I'm making it in my head. 

 

Help much appreciated.  Thanks in advance!

 

Capture.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft Phil_Seamark
Microsoft

Re: Aggregating measure based on dates

If you look at the PBIX file I loaded, I add both.

 

The first is a calculated column to the 4 column table

 

Within Terms = IF('Table1'[Overdue]=0,"Y","N")

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

6 REPLIES 6
Microsoft Phil_Seamark
Microsoft

Re: Aggregating measure based on dates

Hi @meski

 

Is the sample data an example of what you have in a data table?  Or is this summarized from another table?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

meski Frequent Visitor
Frequent Visitor

Re: Aggregating measure based on dates

Hi Phil - this is a simplified representation of fact table data which is transactional, by calendar date and keyed to many other dimensions.  Thx

Microsoft Phil_Seamark
Microsoft

Re: Aggregating measure based on dates

I think I have it,

 

This basic measure sounds like it does the trick. I have attached a sample PBIX

 

Measure = MIN('Table1'[Within Terms])

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

meski Frequent Visitor
Frequent Visitor

Re: Aggregating measure based on dates

Hi Phil - apologies, I haven't been clear.  The last 2 cols in my example are not in the source data.  The boxed column ('Within Terms @ customer level') is the result I am trying to get whereas the column 'Within Terms' is the result I am getting at present. Thx again.

Microsoft Phil_Seamark
Microsoft

Re: Aggregating measure based on dates

If you look at the PBIX file I loaded, I add both.

 

The first is a calculated column to the 4 column table

 

Within Terms = IF('Table1'[Overdue]=0,"Y","N")

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

meski Frequent Visitor
Frequent Visitor

Re: Aggregating measure based on dates

Thanks Phil - works well.  Am npw trying to get a count of how many customers meet this criteria within a period or within a division which I can do but am having issues with getting the count to work at Grand Total level.  In this eg the total should be 1 (ie Cust B). Thanks again in advance.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors