Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
Question : I want to display the Distribution of days per month between two dates by city
data :
ville | agentid | Start | End |
Nice | 1 | 2023-01-01 00:00:00.000 | 2023-01-04 00:00:00.000 |
Nice | 2 | 2023-01-01 00:00:00.000 | 2023-02-27 00:00:00.000 |
Nice | 3 | 2023-01-16 00:00:00.000 | 2023-02-01 00:00:00.000 |
Nice | 4 | 2023-01-18 00:00:00.000 | 2023-02-06 00:00:00.000 |
Paris | 9 | 2023-01-05 11:30:00.000 | 2023-05-10 00:00:00.000 |
Paris | 15 | 2023-01-04 00:00:00.000 | 2023-01-05 10:30:00.000 |
Paris | 98 | 2023-01-03 00:00:00.000 | 2023-01-06 00:00:00.000 |
Paris | 21 | 2023-01-31 00:00:00.000 | 2023-02-21 00:00:00.000 |
Paris | 77 | 2023-01-05 00:00:00.000 | 2023-01-09 00:00:00.000 |
results :
janvier | fevrier | mars | avril | mai | juin | juillet | aout | Septembre | Octobre | Novembre | Decembre | |
Paris | 39 | 49 | 31 | 30 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Nice | 65 | 34 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Total général | 104 | 83 | 31 | 30 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Thank you very much
You are welcome.
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
@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).
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.
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.
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
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.
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
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() ) )
I added 1 to the var MaxDateInContext and it did the trick.
VAR MaxDateInContext = VALUE( MAX( Dates[Date] ) ) + 1
What approach did you do?
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |