Dax Measure help

[vickyprudhvi]

DateDimensionID	JobCloseDate	PolicyNumber	BranchID	Job
20160701	7/1/2016 0:00	A	1	Submission
20160702	7/2/2016 0:00	A	3	Policy Change
20160702	7/3/2016 1:00	C	4	Reinstatement
20160703	7/3/2016 1:00	A	5	Cancellation
20160703	7/3/2016 2:00	B	7	Cancellation
20160703	7/3/2016 3:00	B	8	Cancellation
20160703	7/3/2016 4:00	A	6	Cancellation
20160704	7/5/2016 0:00	A	9	Reinstatement

Hi Friends,

I am trying to write a measure in tabular which gets me count of all the policies whose latest Job is "Cancellation"

latest Job is based on JobCloseDate.

For example

IN Power BI Desktop If I have a slicer on Td_Date[CalendarDate](It is connected to this table using DateDimensionID)

If I select 07/04/2016 on the slicer then the totalCount should be 1

If I select 07/03/2016 on the slicer then the totalCount should be 2

Kindly help me out on this.

[v-haibl-msft]

@vickyprudhvi

In you provided table, the type of DateDimensionID is not standard Date type, so I create a new date column with following formula and create relationship with calendar table with this column.

Date = 
DATE ( LEFT ( Table1[DateDimensionID], 4 ), MID ( Table1[DateDimensionID], 5, 2 ), MID ( Table1[DateDimensionID], 7, 2 ) )

Then we can create another measure to display the result in the card.

Measure = 
VAR CountCurrentDay =
    CALCULATE (
        DISTINCTCOUNT ( Table1[PolicyNumber] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Date] = MAX ( Td_Date[Date] )
                && Table1[Job] = "Cancellation"
        )
    )
RETURN
    (
        IF (
            CountCurrentDay > 0,
            CountCurrentDay,
            CALCULATE (
                DISTINCTCOUNT ( Table1[PolicyNumber] ),
                FILTER (
                    ALL ( Table1 ),
                    Table1[JobCloseDate]
                        = CALCULATE (
                            MAX ( Table1[JobCloseDate] ),
                            FILTER ( ALL ( Table1 ), Table1[JobCloseDate] < MAX ( Table1[Date] ) )
                        )
                        && Table1[Job] = "Cancellation"
                )
            )
        )
)

Best Regards,

Herbert

[vickyprudhvi]

@v-haibl-msft

Thank you for ur detail reply.

What i would like to see is

on 07/04/2016 it should show me 2

and not 1

if I select a 07/04/2016. It should count all the policies from the start to that specific date(which is 07/04/2016)

[v-haibl-msft]

@vickyprudhvi

Please try to use the following updated measure.

Measure_Update = 
VAR CountCurrentDay =
    CALCULATE (
        DISTINCTCOUNT ( Table1[PolicyNumber] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Date] = MAX ( Td_Date[Date] )
                && Table1[Job] = "Cancellation"
        )
    )
RETURN
    (
        IF (
            CountCurrentDay > 0,
            CountCurrentDay,
            CALCULATE (
                DISTINCTCOUNT ( Table1[PolicyNumber] ),
                FILTER (
                    ALL ( Table1 ),
                    Table1[Date] < MAX ( Td_Date[Date] )
                        && Table1[Job] = "Cancellation"
                )
            )
        )
    )

Best Regards,

Herbert

[KGrice]

Using your sample data, I'm able to come up with 2 if I select 7/3/2016, but 0 if I select 7/4/2016. I think that's accurate from what I can see in the table. On 7/4/2016, the only PolicyNumber is A, and it's most recent Job is Reinstatement. With none of the dates selected, I get this for a final product:

 If that looks good, then I did it with 3 measures. You don't have to display all of them in your end result.

MostRecentJob = IF(HASONEVALUE(TableName[PolicyNumber]), 
	CALCULATE(
		VALUES(TableName[Job]), 
		FILTER(ALLEXCEPT(TableName, TableName[PolicyNumber]), TableName[JobCloseDate]=MAX(TableName[JobCloseDate]))
	), BLANK())

isCancelled = IF([MostRecentJob]="Cancellation", 1, 0)

CancelledPolicies = SUMX(VALUES(TableName[PolicyNumber]), [isCancelled])

[vickyprudhvi]

@KGrice

if we select 7/4/2016 on slcier then  it should consider all claims until 7/4/2016

and not just those claims that are on 7/4/2016

[vickyprudhvi]

@KGrice

this is how I want to see.

but as of 7/4/2016 it should show 1 but shows 0

[KGrice]

Hi @vickyprudhvi. What I provided above will work for using with a card without further modification:

 What I'm not sure of is why selecting 7/4/2016 would result in 1 with the data provided. The only Policy on that date is A, with a Job of Reinstatement.

[vickyprudhvi]

Hi @KGrice

The table is Type 2  fact so only delta's and new Policies will be added. so as of 07/04/2016 it should consider all the claims whose max  jobclosedate has Job type cancellation.

[vickyprudhvi]

@KGrice

Thank you for your reply

I want to just see result using card visual that means

measure is alone and not used along PolicyNumber.

hope u understood