Displaying values in between date range

[kkalyanrr]

Hello,

I have a source table which has open and closed dates, now I've connected this table to a calendar table.
And trying to present no.of open jobs in a specific date range.
Currently I'm using this formula 

this is partially successful, but not completely

So techinically if we look for 01/05/2016 to 28/02/2022 - we are getting 0 records(which is correct)
Also from 02/02/2025 to 31/12/2025 - we are getting 0 records(which is correct)
from 01/03/2022 to 01/02/2025 - we are getting 1 record(which is correct)
but from 01/01/2023 to 31/12/2023 - we are getting 0 records( but my expectation is since the dates selected are in between custom open and custom closed dates, we should see 1 record here)
Please help.

[Anonymous · Accepted solution]

Hi @kkalyanrr ,

Based on your description, I did a test. Here is my test table.

I create a measure as follows.

test =
VAR b =
    MAX ( 'Calendar'[Date] )
VAR a =
    MIN ( 'Calendar'[Date] )
RETURN
    SUMX (
        SUMMARIZE (
            FILTER (
                'Display',
                OR (
                    a <= [Custom Open]
                        && [Custom Open] <= b
                        && [Custom Closing] >= b,
                    [Custom Open] <= a
                        && a <= [Custom Closing]
                        && [Custom Closing] <= b
                )
            ),
            Display[Index],
            "abc", DISTINCTCOUNT ( Display[Custom Open] )
        ),
        [abc]
    )

Result:

No error seems to happen. 

If you are still confused about it, please share some sample data and the expected result to have a clear understanding of your question? I can do some tests for you.

You can save your files in some cloud sharing platforms and share the link here.

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Hope that's what you were looking for.

Best Regards,

Yuna

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

[Anonymous]

Hi @kkalyanrr ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem.

Best Regards,

Yuna

[Jihwan_Kim]

Hi, @kkalyanrr 

Please check the below picture and the sample pbix file's link down below.

I simplified your sample, but I hope you can check how I deal with the two date columns in one fact table.

Also, I prefer to have inactive relationships when I face this type of situation. Because I can always turn on the relationship by using userelationship function whenever I want.

Open Job Count =

CALCULATE (

COUNTROWS ( Jobs ),

FILTER (

Jobs,

Jobs[OpenDate] <= MAX ( Dates[Date] )

&& Jobs[CloseDate] >= MIN ( Dates[Date] )

)

)

https://www.dropbox.com/s/9nbls5xh2cerfh9/kkalyanrr.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[amitchandak]

@kkalyanrr , refer one of the two blogs can help you

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...