Make A Formula With Dates.

DouglasWatkins · ‎09-30-2022

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.

v-yanjiang-msft · ‎10-03-2022

Hi @DouglasWatkins ,

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] ) < 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.

Best Regards,
Community Support Team _ kalyj

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

DouglasWatkins · ‎10-06-2022

I tried yours but it's not working. I think the issus is that my data table doesn't have a complete date range of January 1st to December 31st.

lbendlin · ‎10-06-2022

It doesn't need to. What needs to have a complete range of dates is the Calendar table in your data model. If you don't have that then you need to add one.

lbendlin · ‎10-01-2022

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

DouglasWatkins · ‎10-05-2022

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. It seems all the power bi formulas require a complete January 1 to December 31 calendar. My data doesn't have that and I can't edit it because the data will updated regularly.