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 (Fisca...
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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

Share the download link of the PBI file.

spandy34's avatar
spandy34
Responsive Resident
3 years ago

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"))
  • Anonymous's avatar
    Anonymous
    Not applicable
    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