count measure between unrelated tables

[scabral]

hi,

i have a Claim table and a Date table.  They are not related to each other, only through the Claim Payment Fact.  However, because some claims do not have a Payment, not all claims are represented in the Claim Payment Fact.

What i want to do is count how many Claims were open (via a count of Claim Number from the Claim Table) during a particular Month and Year (i.e Dec 2019, Jan 2020, etc...).  I have a column in the Date table called Date Month Year so i can use that on the visual.

What i am having trouble with is how to count the number of claims for each month year when i don't have a direct relationship between the Claim and Date tables.  I have a Claim Status in the Claim Table that should be either "Open" or "Closed", and i also have a Date Of Loss column in the Claim table as well that tells me when the claim occurred and a Claim Close Date column for when the claim was closed.

So what i need is for each Month Year in the Date table, see how many claims from the Claim table that occurred before that date and status were open.  So for example if i only had 2 claims today in the claim table and claim 1 occurred in Jan 2020 and claim 2 occurred in Feb 2020 and they were both Open, i should see the following when i drag Month Year from Date table and the measure of count open claims:

Month Year     Count Open Claims

Jan 2020          1

Feb 2020         2

March 2020     2

Now if Claim 1 closed in March 2020, then i should see this instead:

Month Year     Count Open Claims

Jan 2020          1

Feb 2020         2

March 2020     1

thanks

scott

[mahoneypat]

Please try an expression like this to get your result.  It will count the number of open claims during date period in scope.

NewMeasure =
VAR mindate =
    MIN ( Date[Date] )
VAR maxdate =
    MAX ( Date[Date] )
RETURN
    COUNTROWS (
        FILTER (
            Claim,
            Claim[Date of Loss] <= maxdate
                && Claim[Claim Close Date] >= mindate
        )
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[scabral]

This version gets me the same numbers as my original code, but still gives me the future months (which i don't want):

VAR maxdate =

MAX ( 'Date Of Loss'[Date of Loss Calendar Date] )

RETURN

COUNTROWS (

FILTER (

'Claim Adjustment File',

'Claim Adjustment File'[Date of Loss] <= maxdate

&& 'Claim Adjustment File'[Claim Status] = "Open"

)

)

+

COUNTROWS (

FILTER (

'Claim Adjustment File',

'Claim Adjustment File'[Date of Loss] <= maxdate

&& 'Claim Adjustment File'[Claim Status] = "Closed"

&& 'Claim Adjustment File'[Claim Close Date] > maxdate

)

)

[scabral]

Hi,

so what is missing here is claims that are still open (basically claims without a closed date).  What i need is sort of a cumulative count of open claims for each time period.  So for each period i need claims that are currently open for that period (so if we are in Jan 2020, it would be claims that have a loss date < 1-31-2020 and are in status "Open", and also claims that are in  a status of "closed" but have a close date > 1-31-2020 since those claims were open during Jan 2020. 

My script above seems to do it, but the issue i had was that i was getting values for future months because my date table has dates for all of 2020.  I was trying to figure out how to either remove those future months or show blanks for the values.

[amitchandak]

@scabral , This data dows not look like base data. Can you share sample data and sample output in table format?

refer if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[scabral]

HI,

so i was able to get some DAX working like this which gives me the number of open claims within the period as well as closed claims that were open within the period but closed after the period:

Count Cumulative Open Claims =

VAR MaxDate = MAX ( 'Date Of Loss'[Dim FM Global Date Identifier] ) -- Saves the last visible date

VAR OpenWithinPeriod =

CALCULATE (

[Count of Claims],

'Claim Adjustment File'[Date of Loss ID] <= MaxDate, -- Where date is before the last visible date

ALL ( 'Date Of Loss' ), -- Removes any other filters from Date

'Claim Adjustment File'[Claim Status]="Open") --get only the open claims before the maxdate

VAR ClosedWithinPeriod =

CALCULATE (

[Count of Claims],

'Claim Adjustment File'[Date of Loss ID]<= MaxDate, -- Where date is before the last visible date

ALL ( 'Date Of Loss' ), -- Removes any other filters from Date

'Claim Adjustment File'[Claim Status]="Closed",

'Claim Adjustment File'[Claim Close Date ID]>MaxDate --get the claims that were closed after the maxdate since they were still open at the time

)

RETURN

OpenWithinPeriod + ClosedWithinPeriod

However, my claim data should only be up to January 2020.  I don't have any claims created after that, but my visual is showing dates up to Dec 2020 because the Date table has rows up to Dec 2020.  How do i show blanks or get rid of those 'future' rows?

need to get rid of highlighted rows or show blank counts