YTD Measure

[spandy34]

I want to be able to Count the distinct values in the Claim Ref field of all the records where the ClassofBusinessCode = MV

Policycode contains MV

And the Type Code = R1

For Year to Date (Fiscal Year so 01/04/2023 to today – 02/08/2023)

I have a date table where the field  to 'INS_Fin_Calendar Main Claim Data Payment Date'[Date] is linked to the INS_Main Claim Data [Date] fields are linked.

I was given the Measure below but it is calculating 11 records when there is actually only 2 distinct records 

Can anyone see where I might be going wrong with the measure please or suggest another way of writing this?

z_Recoveries_Fiscal_YTD Recoveries No Successful Recoveries Motor =

VAR LastDayAvailable =

    MAX ( 'INS_Fin_Calendar Main Claim Data Payment Date'[Date] )

VAR LastFiscalYearAvailable =

    MAX ( 'INS_Fin_Calendar Main Claim Data Payment Date'[FiscalMIndex] )

VAR Result =

    CALCULATE (

        DISTINCTCOUNT('INS_Main Claim Data'[ClaimRef]),

        FILTER (

            'INS_Main Claim Data',

            'INS_Main Claim Data'[ClassOfBusinessCode] = "MV"

                && CONTAINSSTRINGEXACT ( [PolicyCode], "MV" )

                && 'INS_Main Claim Data'[TypeCode]="R1"

        ),

        'INS_Fin_Calendar Main Claim Data Payment Date'[Date] <= LastDayAvailable,'INS_Fin_Calendar Main Claim Data Payment Date'[Fiscal Year]=YEAR(TODAY()-365) & "-" & FORMAT(TODAY (), ("yyyy"))

    )

RETURN

    Result

@mlsx4 @Pragati11 @tamerj1 @Greg_Deckler 

[spandy34]

Thank you that has worked.

[Greg_Deckler]

@spandy34 Probably an issue because you have a single table involved.

[spandy34]

Could you see any reason why the following wouldnt work?  Please excuse my knowledge.  I am quite new to Meausres

    CALCULATE (

        DISTINCTCOUNT('INS_Main Claim Data'[ClaimRef]),

        FILTER (

            'INS_Main Claim Data',

            'INS_Main Claim Data'[ClassOfBusinessCode] = "MV"

                && CONTAINSSTRINGEXACT ( [PolicyCode], "MV")

                && 'INS_Main Claim Data'[TypeCode]IN {"R1","R2"}

                ),

    DATESYTD('INS_Fin_Calendar Main Claim Data Payment Date'[Date],"01/04"))

[spandy34]

I have a Date Table and also a Main Data Table which are linked so I dont have a single table.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[spandy34]

Hi there thank you for responding. I wont be able to share the link of the PBI file for security reasons.  

Could you see any reason why the following wouldnt work? I placed the start date of the year and the data seems to work but I didnt want to get caught out if you felt there would be an issue with the next financial year . Please excuse my knowledge.  I am quite new to Meausres

    CALCULATE (

        DISTINCTCOUNT('INS_Main Claim Data'[ClaimRef]),

        FILTER (

            'INS_Main Claim Data',

            'INS_Main Claim Data'[ClassOfBusinessCode] = "MV"

                && CONTAINSSTRINGEXACT ( [PolicyCode], "MV")

                && 'INS_Main Claim Data'[TypeCode]IN {"R1","R2"}

                ),

    DATESYTD('INS_Fin_Calendar Main Claim Data Payment Date'[Date],"01/04"))

[v-cgao-msft · Accepted solution]

Hi @spandy34 ,

Make sure that the date format in the function matches the date format used in your data model. In your case, it should be "04/01"

Please try:

CALCULATE (
    DISTINCTCOUNT('INS_Main Claim Data'[ClaimRef]),
    FILTER (
        'INS_Main Claim Data',
        'INS_Main Claim Data'[ClassOfBusinessCode] = "MV"
            && CONTAINSSTRINGEXACT ( [PolicyCode], "MV")
            && 'INS_Main Claim Data'[TypeCode] IN {"R1","R2"}
    ),
    DATESYTD('INS_Fin_Calendar Main Claim Data Payment Date'[Date], "04/01")
)

Or:

CALCULATE (
    DISTINCTCOUNT('INS_Main Claim Data'[ClaimRef]),
    FILTER (
        'INS_Main Claim Data',
        'INS_Main Claim Data'[ClassOfBusinessCode] = "MV"
            && CONTAINSSTRINGEXACT ( [PolicyCode], "MV")
            && 'INS_Main Claim Data'[TypeCode] IN {"R1","R2"}
    ),
    DATESYTD('INS_Fin_Calendar Main Claim Data Payment Date'[Date], "04-01")
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum