cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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',

&& 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

1 ACCEPTED SOLUTION
Community Support

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"

``````CALCULATE (
DISTINCTCOUNT('INS_Main Claim Data'[ClaimRef]),
FILTER (
'INS_Main Claim Data',
&& 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',
&& 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 7
Responsive Resident

Thank you that has worked.

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Responsive Resident

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',
&& CONTAINSSTRINGEXACT ( [PolicyCode], "MV")
&& 'INS_Main Claim Data'[TypeCode]IN {"R1","R2"}
),
DATESYTD('INS_Fin_Calendar Main Claim Data Payment Date'[Date],"01/04"))
Responsive Resident

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

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Responsive Resident

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',
&& CONTAINSSTRINGEXACT ( [PolicyCode]"MV")
&& 'INS_Main Claim Data'[TypeCode]IN {"R1","R2"}
),
DATESYTD('INS_Fin_Calendar Main Claim Data Payment Date'[Date],"01/04"))
Community Support

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"

``````CALCULATE (
DISTINCTCOUNT('INS_Main Claim Data'[ClaimRef]),
FILTER (
'INS_Main Claim Data',
&& 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',
&& 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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors