Showing results for 
Search instead for 
Did you mean: 
Roniiah Frequent Visitor
Frequent Visitor

DAX formula for value at point in time (dynamic)

This might not be as diffifult as I think, but I am having trouble to get around it.

I have a table that consist of cases: one row per case. Each case has a start date and an end date. I have added a quantity column  for clarity so that I can use SUM instead of COUNT.

What I need to do is to calculate ONGOING cases at the end of the chosen month. i.e where start date is on or before the last day in the chosen month, and with an end date that is AFTER the last day in the chosen month.

It was easy enough to create a test measure for a specific month, but I am not able to make it dynamic to work with year and month filter/slicer.

Any suggestions?

Here's my test measure:




Gokul Member

Re: DAX formula for value at point in time (dynamic)

can u please post some sample data and expected results

Roniiah Frequent Visitor
Frequent Visitor

Re: DAX formula for value at point in time (dynamic)



Se pictures for sample data. I have loaded these tables into my Power BI (only two of several as part of my model). Datetable is loaded twice, once as RegDate and once as ClosingDate.


What I want to do is to be able to use the year and month from one of the date tables to filter 'Ongoing cases' measure. If i choose Jan 2018 I want to see the sum of all cases with a regdate on or before 31st of jan AND with a closing date after 31st jan. Same for feb, march, april etc.

Community Support Team
Community Support Team

Re: DAX formula for value at point in time (dynamic)

hi, @Roniiah

You may try this measure 


Measure = CALCULATE(COUNTA(Table1[ID]),FILTER(Table1,Table1[RegDate ]<=MAX('Date'[Date])&&Table1[ClosingDate]>MAX('Date'[Date])))

Note: Do not create the relationship between data table with date table

this is based on the year and month you just select one value.

If you select two or more the year and month, what is the logic ?

For example:

If you choose Jan 2018 and Feb 2018 .


Best Regards,




Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 229 members 2,818 guests
Please welcome our newest community members: