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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Counting rows between two dates

I want to create a measure that checks to see if there is an entry between a certain set of dates.  The dates are the absolute max date of all entries created, and one year prior to that.  The maximum entry date in my data is 12/9/2021, so it should be looking between 12/9/2020-12/9/2021, however it's not working properly.  It is telling me that certain accounts have entries made when they are outside of those dates.

 

Here is my formula:

a_Test Home Office =
var maxdate = CALCULATE(MAX('Usage Statistics'[CreateDate]),ALL('Usage Statistics'))
var startdate = CALCULATE(maxdate-365,ALL('Usage Statistics'))
return
CALCULATE(COUNTAX('Usage Statistics','Usage Statistics'[ObjectType]),'Usage Statistics'[ObjectType]="NB Exhibit Exhibit",'Usage Statistics'[CreateDate]<=maxdate,'Usage Statistics'[CreateDate]>=startdate,'Usage Statistics'[Group]="Home Office")+CALCULATE(COUNTAX('Usage Statistics','Usage Statistics'[ObjectType]),'Usage Statistics'[ObjectType]="Main Exhibit Account",'Usage Statistics'[CreateDate]<=maxdate,'Usage Statistics'[CreateDate]>=startdate,'Usage Statistics'[Group]="Home Office")
2 REPLIES 2
v-jingzhang
Community Support
Community Support

Hi @Anonymous 

 

You could try this measure

a_Test Home Office =
VAR maxdate = CALCULATE ( MAX ( 'Usage Statistics'[CreateDate] ), ALL ( 'Usage Statistics' ) )
VAR startdate = EDATE ( maxdate, -12 )
RETURN
    CALCULATE (
        COUNT ( 'Usage Statistics'[ObjectType] ),
        'Usage Statistics'[ObjectType] IN { "NB Exhibit Exhibit", "Main Exhibit Account" },
        'Usage Statistics'[CreateDate] <= maxdate,
        'Usage Statistics'[CreateDate] >= startdate,
        'Usage Statistics'[Group] = "Home Office"
    )

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

lbendlin
Super User
Super User

var startdate = CALCULATE(max('Usage Statistics'[CreateDate]),ALL('Usage Statistics'),'Usage Statistics'[CreateDate])>=EDATE(maxdate,-12))

 

And then you can use a DATESBETWEEN filter.

Helpful resources

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

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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