Forum Discussion

spandy34's avatar
spandy34
Responsive Resident
3 years ago
Solved

YTD Measure

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 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

7 Replies

    • spandy34's avatar
      spandy34
      Responsive Resident
      3 years ago

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

    • spandy34's avatar
      spandy34
      Responsive Resident
      3 years ago

      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"))