Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Count based on Min / Max Date by Quarter

So I have two measures that calculate the min and max date by quarter. These measures reside within what's basically a calendar table. When I add the quarter field, I can add the min / max dates to t...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Anonymous, dkay84_PowerBI,

     

    Thanks for both of your responses. I ended up going a different route. Anonymous, I could never get your example to work properly. 

     

    I ended up making a secondary table that only included the max and min dates (SQL Query). I then used the LOOKUPVALUE function to pull that min/max date respectively in my overall table. I then compared the file date in that table to the min/max date and used "yes" or "no" if it matched that date (as another column in the raw data file).

     

    I then more simply created a calculation (measure) that filtered in those that were "yes". 

     

    I appreciate your assistance in responding. 

     

    Jonathan 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Anonymous,


I'd like to suggest you use maxx or minx function to get the minfiledate/maxfiledate per quarter.

 

Sample:

 

Count of MinFileDate= 
var minDate= MINX(FILTER(ALL(Salesforce),[Date].[QuarterNo]=MAX([Date].[QuarterNo])&&[Date].[Year]=MAX([Date].[Year])),[Date])
return
CALCULATE(Count(Salesforce[Id]), FILTER(CalendarTable,CalendarTable[FILE_DATE] = minDate))


Count of MaxFileDate= 
var MaxDate= MAXX(FILTER(ALL(Salesforce),[Date].[QuarterNo]=MAX([Date].[QuarterNo])&&[Date].[Year]=MAX([Date].[Year])),[Date])
return
CALCULATE(Count(Salesforce[Id]), FILTER(CalendarTable,CalendarTable[FILE_DATE] = MaxDate))

 

Regards,

Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Anonymous, dkay84_PowerBI,

 

Thanks for both of your responses. I ended up going a different route. Anonymous, I could never get your example to work properly. 

 

I ended up making a secondary table that only included the max and min dates (SQL Query). I then used the LOOKUPVALUE function to pull that min/max date respectively in my overall table. I then compared the file date in that table to the min/max date and used "yes" or "no" if it matched that date (as another column in the raw data file).

 

I then more simply created a calculation (measure) that filtered in those that were "yes". 

 

I appreciate your assistance in responding. 

 

Jonathan