Counting Between Dates

[Syndicate_Admin]

Good day, I'm new to power bi and I'm having problems with a formula that at first glance seems simple. I have already seen other similar posts in this forum, but I have not found a response.

I have a table with 3 columns: services rendered, start date and end date.

I need the formula to show the number of active services per year.

My formula is as follows:

Historical Assets = CALCULATE([Services],FILTER('Services','Services'[Start Date]&&'Services'[EndDate]))

I tested it using counts, countrows, using MIN and MAX and many more variants. But what counts are the services that started in that year, it does not take into account if the service started years before but is still in force.

For example, Service1 is in force from 2016 to 2024, but it only counts it in 2016, and so on.

I will thank you for your help.

[Anonymous]

Hi, @Syndicate_Admin 

Based on your information, I create a sample table:

Then create a new measure and try the following DAX expression:

Historical Assets = 
VAR MinYear = YEAR(MIN('Table'[Start Date]))
VAR MaxYear = YEAR(MAX('Table'[End Date]))
RETURN
SUMX(
    GENERATESERIES(MinYear, MaxYear, 1),
    VAR CurrentYear = [Value]
    RETURN
    CALCULATE(
        COUNTROWS('Table'),
        'Table'[Start Date] <= DATE(CurrentYear, 12, 31) &&
        'Table'[End Date] >= DATE(CurrentYear, 1, 1)
    )
)

Here is my preview:

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[Syndicate_Admin]

Thank you very much for your answer, but the data you provide are not correct. For example, in 2016 there is only one service.

The correct answers would be:

2016: 1
2017: 5
2018: 15
2019: 24
2020: 22
2021: 22
2022: 27
2023: 33
2024: 31

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...