Measure for values in a date range

[RichOB]

Hi, I have a table below that shows the number of instances that happened on a specific date in the next row. I need 2 measures that show:

2023 - 4798 instances

2024 - 3555 instances

Instances	Date
1157	10/04/2023
1527	10/07/2023
1157	10/10/2023
957	10/01/2023
1126	10/04/2023
1163	10/07/2023
1066	10/10/2023

How can this be done please?

[bhanu_gautam · Accepted solution]

@RichOB , Try using 

Instances_2023 =
CALCULATE(
SUM('Table'[Instances]),
YEAR('Table'[Date]) = 2023
)

Instances_2024 =
CALCULATE(
SUM('Table'[Instances]),
YEAR('Table'[Date]) = 2024
)

[bhanu_gautam · Accepted solution]

@RichOB , Try using 

2023_Instances =
CALCULATE(
COUNT('Table'[Instances]),
FILTER(
'Table',
'Table'[Date] >= DATE(2023, 1, 4) && 'Table'[Date] <= DATE(2023, 3, 31)
)
)

[Anonymous · Accepted solution]

Hi @RichOB 

Thank you very much bhanu_gautam for your prompt reply.

I tested your measure and bhanu_gautam's measure separately and both give correct results. If your code does not work, check that the data type of the date is correct.

If there are any potential filters applied to your report that may also affect the calculation. You may consider using the ALL function to ignore these filters.

Filter ALL 2023_Instances = 
CALCULATE(
COUNT('Table'[Instances]),
FILTER(
ALL('Table'),
'Table'[Date] >= DATE(2023, 1, 4) && 'Table'[Date] <= DATE(2024, 3, 31)
)
)

Regards,

Nono Chen

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

[bhanu_gautam · Accepted solution]

@RichOB , Try using 

Instances_2023 =
CALCULATE(
SUM('Table'[Instances]),
YEAR('Table'[Date]) = 2023
)

Instances_2024 =
CALCULATE(
SUM('Table'[Instances]),
YEAR('Table'[Date]) = 2024
)

[RichOB]

Hi @bhanu_gautam Thanks for this.

How would I get the count of instances between 1/4/2023 - 3/31/2024?
Usually I would do this measure like below, but it's not working:

2023_Instances = (

    CALCULATE(

        COUNT('Table' [Instances]),

        'Table'[Date]>=DATE(2023,4,1),

        'Table'[Date]<=DATE(2024,3,31)

    ))

Thanks

[bhanu_gautam · Accepted solution]

@RichOB , Try using 

2023_Instances =
CALCULATE(
COUNT('Table'[Instances]),
FILTER(
'Table',
'Table'[Date] >= DATE(2023, 1, 4) && 'Table'[Date] <= DATE(2023, 3, 31)
)
)