cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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:

TEST_OngoingCases_dec2018:=

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

 

3 REPLIES 3
Gokul Member
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)

cases.JPGdate.JPG

 

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.

Highlighted
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,

Lin

 

 

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.