Make A Formula With Dates.

Question

I have an Excel file I loaded into the program. There are 2 columns that I need to use to create something.

I need to count the number of IDs in each day. Each day could be written in several rows. Each day could have several IDs but each ID will only be in one day.

I need to figure out two things. I need to have a count of distinct IDs per year as of today (Which changes). The list below would show 6 distinct IDs. I also need to have a count of distinct IDs per fiscal year as of today. So if I look at it October 1st, I need the counts from January 1st to October 1st, and July 1st (The start of my fiscal year) to October 1st.

Dates ID

1/1/20 345

1/1/20 230

1/1/20 869

2/3/21 349

2/3/21 983

3/2/22 567

Thanks for all of your help.

lbendlin · Answer

This sounds pretty straightforward.&nbsp;Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?

v-yanjiang-msft · Answer

Hi&nbsp;DouglasWatkins&nbsp;,
According to your description, here's my solution.
1.Count distinct IDs in all the years. Create a measure.
Measure =
CALCULATE ( DISTINCTCOUNT ( 'Table'[ID] ) )

Result:

2.Count distinct IDs in each year. Create a measure.
Measure2 =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[ID] ),
    YEAR ( 'Table'[Dates] ) = YEAR ( MAX ( 'Table'[Dates] ) )
)

Result:

3.Count distinct IDs in each fiscal year. First create a calculated column.
FiscalYear =
IF ( MONTH ( [Dates] ) &lt; 7, YEAR ( [Dates] ), YEAR ( [Dates] ) + 1 )

Then create a measure:
Measure3 =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[ID] ),
    'Table'[FiscalYear] = MAX ( 'Table'[FiscalYear] )
)

Result:

I attach my sample below for your reference.
&nbsp;
Best Regards,Community Support Team _ kalyj
If this post&nbsp;helps,&nbsp;then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

douglaswatkins · Answer

I've tried a few things but the issue with the other answers working for me is my date column is not a complete set of dates.&nbsp; It seems all the power bi formulas require a complete January 1 to December 31 calendar.&nbsp; My data doesn't have that and I can't edit it because the data will updated regularly.