distinct count by calendar year

[OPS-MLTSD]

Hello,

I am trying to do a distinct count of claim numbers by calendar year.

As you can see from my table, I have claims numbers and accident year but I would like to create  columns that shows the claim numbers by year so the columns would be called "2017 claims", "2018 claims" etc.

I tried to use the DAX code: 2017 msd count = CALCULATE (DISTINCTCOUNT(Query1[Claim Number]),DATESBETWEEN(Query1[Accident Date]), 2017-01-01,2017-12,31)

but I am only getting blank results. If someone could please help me figure out, that would be great!

[v-xicai]

Hi @OPS-MLTSD ,

In the most common use case, dates of DATESBETWEEN function is a reference to the date column of a marked date table.

You may create a calendar table first of all, then create relationship with your fact table on the date field.

Calendar= CALEANDARAUTO()

Then you may use the Calendar[Date] in DATESBETWEEN function like codes below. 

2017 msd count = CALCULATE (DISTINCTCOUNT(Query1[Claim Number]),DATESBETWEEN(Calendar[Date]), DATE(2017,1,1) ,DATE(2017,12,31)))

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@OPS-MLTSD , Try like

 2017 msd count = CALCULATE (DISTINCTCOUNT(Query1[Claim Number]),filter(Query1, Query1[Accident Date] >= date(2017,01,01) 
 and Query1[Accident Date] <=Date(2017,12,31)))

you can also use time intelligence like given example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

3rd Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-3,Year)),"12/31")) //in 2020 it will be 2017

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[OPS-MLTSD]

this dax code is not working

[amitchandak · Accepted solution]

@OPS-MLTSD , My Bad. Try

2017 msd count = CALCULATE (DISTINCTCOUNT(Query1[Claim Number]),filter(Query1, Query1[Accident Date] >= date(2017,01,01) 
 && Query1[Accident Date] <=Date(2017,12,31)))

You are trying like a column. But this like a measure