Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Distribution of days per month between two dates

Hi,

 

I recently stumbled upon a tutorial on how to distribute the number of days per month. It's all good except for the date ranges that fall between two months. For example, Patient 107 is showing 8 days for January but it should be 9 days. I'm trying to go through the formula of the measure but I can't seem to get my head around it. Any help would be much appreciated. Thanks all!Capture.PNG

 

Sample PBIX

1 ACCEPTED SOLUTION

Hi @Anonymous 

 

check this.

 

 

Occupied Days WithIn Month = 
VAR AdmitDate = VALUE( SELECTEDVALUE( Table1[Admit] ) )
VAR DepartureDate = VALUE( SELECTEDVALUE( Table1[Departure] ) )
VAR MinDateInContext = VALUE( MIN( Dates[Date] ) )
VAR MaxDateInContext = VALUE( MAX( Dates[Date] ) )

RETURN
IF( AND( AdmitDate < MinDateInContext; DepartureDate > MinDateInContext ) ;
        MIN( DepartureDate; MaxDateInContext ) - MinDateInContext;
            IF( AND( AND( AdmitDate > MinDateInContext; AdmitDate < MaxDateInContext ); DepartureDate > MinDateInContext );
                 MIN( DepartureDate; MaxDateInContext + 1 ) - AdmitDate; 
                    BLANK() ) )

 

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

21 REPLIES 21
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi

we can't find your file, can you upload it again please 

thanks

 

Hi,

I do not have that file.  Share some data, explain the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Question : I want to display the Distribution of days per month between two dates by city

data :

villeagentidStartEnd
Nice12023-01-01 00:00:00.0002023-01-04 00:00:00.000
Nice22023-01-01 00:00:00.0002023-02-27 00:00:00.000
Nice32023-01-16 00:00:00.0002023-02-01 00:00:00.000
Nice42023-01-18 00:00:00.0002023-02-06 00:00:00.000
Paris92023-01-05 11:30:00.0002023-05-10 00:00:00.000
Paris152023-01-04 00:00:00.0002023-01-05 10:30:00.000
Paris982023-01-03 00:00:00.0002023-01-06 00:00:00.000
Paris212023-01-31 00:00:00.0002023-02-21 00:00:00.000
Paris772023-01-05 00:00:00.0002023-01-09 00:00:00.000

 

 

results :

   janvier  fevrier   mars   avril   mai   juin   juillet   aout  Septembre  Octobre  Novembre  Decembre
Paris39493130100000000
Nice65340000000000
Total général104833130100000000

Hi,

Similar problem solved in the attached file.  Please study the solution there and apply the formulas to your data/file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you very much

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Will this work if patient is tied up to one incident as in my question here? One incident may have multiple lost work days and this needs to be distrubuted per location per month. In some cases I want to count and distrubute per month until I have an End Date

https://community.powerbi.com/t5/Power-Query/How-to-calculate-dates-using-start-and-end-date-distrib...

@Ashish_Mathur i downloaded your pbix file, but i can't see where you actually use the code you've put in?

 

Where does that go?

Hi,

The M code transformations can be viewed by going to Home > Edit Queries.  The DAX formulas can be viewed in the Data table of the Fields pane (extreme right hand side).


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks, I think I figured it out.

 

Is it possible to use this or something like it in a bar chart, rather than a matrix? 

Hi @Anonymous ,

 

why sould it be 9?

31 - 23 = 8

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

Hi @mwegener ,

 

The last day of the month is not being counted if you take the numbers literally as you mentioned.

There are 34 days between the two dates but if you add the numbers you'll be short 1 day. 

@Anonymous 

 

If you want to include the admit date to the calculation, subtract 1 day from AdmitDate variable as below.

 

VAR AdmitDate = VALUE(SELECTEDVALUE( Table1[Admit] ) )-1
 
If this helps, mark it as a solution
Kudos are good too
Connect on LinkedIn
Anonymous
Not applicable

Hi @VasTg ,

That was what I initially did, but unfortunately, I will still get the wrong date presentation as the last date of departure date should not be counted. Adding 1 in MaxDateInContext did the trick.

VAR MaxDateInContext = VALUE( MAX( Dates[Date] ) ) + 1

 

@mwegener 

I'm trying to understand the formula but I can't seem to get a complete idea. If you have time, appreciate it if you could walk me through the formula.

Hi @Anonymous 

 

check this.

 

 

Occupied Days WithIn Month = 
VAR AdmitDate = VALUE( SELECTEDVALUE( Table1[Admit] ) )
VAR DepartureDate = VALUE( SELECTEDVALUE( Table1[Departure] ) )
VAR MinDateInContext = VALUE( MIN( Dates[Date] ) )
VAR MaxDateInContext = VALUE( MAX( Dates[Date] ) )

RETURN
IF( AND( AdmitDate < MinDateInContext; DepartureDate > MinDateInContext ) ;
        MIN( DepartureDate; MaxDateInContext ) - MinDateInContext;
            IF( AND( AND( AdmitDate > MinDateInContext; AdmitDate < MaxDateInContext ); DepartureDate > MinDateInContext );
                 MIN( DepartureDate; MaxDateInContext + 1 ) - AdmitDate; 
                    BLANK() ) )

 

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Will this work if patient is tied up to one incident as in my question here? In some cases I want to count and distrubute per month until I have an End Date

https://community.powerbi.com/t5/Power-Query/How-to-calculate-dates-using-start-and-end-date-distrib...

Anonymous
Not applicable

Sweet!

 

What I did was trying to deduct and add from the InDate and OutDate but it didn't help me. 

Slightly modified and added +1 as well to the first IF instance.

Occupied Days WithIn Month = 
VAR AdmitDate = VALUE( SELECTEDVALUE( Table1[Admit] ) )
VAR DepartureDate = VALUE( SELECTEDVALUE( Table1[Departure] ) )
VAR MinDateInContext = VALUE( MIN( Dates[Date] ) )
VAR MaxDateInContext = VALUE( MAX( Dates[Date] ) )

RETURN
IF( AND( AdmitDate < MinDateInContext; DepartureDate > MinDateInContext ) ;
        MIN( DepartureDate; MaxDateInContext + 1) - MinDateInContext;
            IF( AND( AND( AdmitDate > MinDateInContext; AdmitDate < MaxDateInContext ); DepartureDate > MinDateInContext );
                 MIN( DepartureDate; MaxDateInContext + 1 ) - AdmitDate; 
                    BLANK() ) )

 

@mwegener @Anonymous 

 

Guys,

 

I tried the DAX and tested other Patient ID. If my understanding is correct, we should include the admit date per the initial ask. So for Patient 112, we will be missing the count for Jan 2018.

 

Capture3.PNG

 

Let me know if I am wrong.

Connect on LinkedIn
Anonymous
Not applicable

@VasTg 

I added 1 to the var MaxDateInContext and it did the trick.

 

VAR MaxDateInContext = VALUE( MAX( Dates[Date] ) ) + 1

 

What approach did you do?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.