DAX formula for value at point in time (dynamic)

[Anonymous]

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:

TEST_OngoingCases_dec2018:=

CALCULATE(SUM(FactCase[Quantity]);FactCase[RegDate_FK]<=20181231;FactCase[ClosingDate_FK]>20181231)

[Gokul]

can u please post some sample data and expected results

[Anonymous]

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.

[v-lili6-msft]

hi, @Anonymous

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,

Lin