Need DAX pattern that may be used for a multitude of uses

[rwegmann]

Employment table with start and end dates (person is still employed if end date is blank):

See table below.

Trying to build visual showing number of persons employed by month.  Visual would look like this:

Hopefully this is all you need.  Just can't figure this out.  Looking for DAX measure, I think, that would do this.

Thanks in advance for anyone willing to help.

Rick

[Greg_Deckler]

@rwegmann I think that a varation of this shold do, it is essentially a time interval problem:

Take a look at these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[rwegmann]

@Greg_Deckler I just quickly scanned the first link you provided and I think it can be applied to my problem!!   I've been messing with this on and off for months with no success!  There was no way I was going to put the various functions together in this fashion to solve the problem.   SO THANK YOU SO  MUCH for your incredibly quick response to my post!  I hope to repay someone on the forum with the same kindness, although I'm quite an amateur at this!  🙂

P.S.  The only thing I need to do is create a cumulative visual NOT single bars per month.  But that should be relatively straight forward I would think.  Thank you.

[v-kelly-msft · Accepted solution]

Hi @rwegmann ,

First create a date table as below:

date table = CALENDAR(MIN('Table'[Start date ]),MAX('Table'[End date]))

Then create a measure as below:

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[employer] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Start date ] <= MAX ( 'date table'[Date] )
            && 'Table'[_Enddate] >= MAX ( 'date table'[Date] )
    )
)

And you will see:(pay attention:I just took part of your sample data for test)

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[rwegmann]

Hello @v-kelly-msft and everyone:

Some of you may recall in August Kelly solved a persistent DAX problem for me.  I am very grateful to her as I've been using it since that time and it works well.   

HOWEVER, I am at a loss for how it works.   I have spent hours looking at the various functions, operators, etc.   I have a rudimentary understanding of Evaluation Contexts, including Context Transition.  What is confusing is there is no relationship between the date table and the fact table, but somehow filtering is occuring on the visual.  

Understanding this will help in solving some other issues I'm dealing with.

Thanks to anyone in advance for your kindness in assisting.

Rick

[rwegmann]

Hello everyone:

Shown below is a detailed analysis of the resulting table from the FILTER function.  When passed to the VISUAL, it isn't clear WHY it should work.   There is NO relationship between the two tables AND HOW does the EXCLUDED rows get passed to the visual.   Spent hours trying to understand this.   I stopped the analysis AFTER I couldn't understand how the EXCLUDED ROW (a6) was shown in the VISUAL.

Anyone interested in weighing in to assist my misguided thinking, please feel free to do so.  Thanks, Rick

[rwegmann]

Hi Kelly:   @v-kelly-msft Thank you so much for adding to the thread. I believe the information you provided is the right solution.  I need some time this weekend to implement, which I will do and let you know.  The detail and the pbix file you provided is incredibly helpful.  It answered the question I had with respect to any type of relationship between the two tables, which there is none.   As I told Greg @Greg_Deckler , I hope to repay the favor to someone in the future.   Thank you for your kindness.

[v-kelly-msft]

Hi @rwegmann ,

Glad to hear it.

If you feel it's a solution,would you pls mark the reply as answered to let more people find it?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[rwegmann]

Kelly:   Thank you again for this.   Was just able to find some time to implement.   Only slight change and that was calculating the _end date.  Changed from fixed date to "TODAY()" to prevent graph going past today's date.   Great simple solution!  Thanks also to everyone who contributed to this thread.