Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
spandy34
Responsive Resident
Responsive Resident

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 

1 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

View solution in original post

7 REPLIES 7
spandy34
Responsive Resident
Responsive Resident

Thank you that has worked.

Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

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

Hi,

Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.