Forum Discussion
Measure for values in a date range
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?
RichOB , Try using
Instances_2023 =
CALCULATE(
SUM('Table'[Instances]),
YEAR('Table'[Date]) = 2023
)Instances_2024 =
CALCULATE(
SUM('Table'[Instances]),
YEAR('Table'[Date]) = 2024
)RichOB , Try using
2023_Instances =
CALCULATE(
COUNT('Table'[Instances]),
FILTER(
'Table',
'Table'[Date] >= DATE(2023, 1, 4) && 'Table'[Date] <= DATE(2023, 3, 31)
)
)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.
4 Replies
RichOB , Try using
Instances_2023 =
CALCULATE(
SUM('Table'[Instances]),
YEAR('Table'[Date]) = 2023
)Instances_2024 =
CALCULATE(
SUM('Table'[Instances]),
YEAR('Table'[Date]) = 2024
)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)))ThanksRichOB , Try using
2023_Instances =
CALCULATE(
COUNT('Table'[Instances]),
FILTER(
'Table',
'Table'[Date] >= DATE(2023, 1, 4) && 'Table'[Date] <= DATE(2023, 3, 31)
)
)
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.
