date divison
1 Topic
Filter per last entry, per month
I'm working with a hospital database to make a dashboard that shows both a daily status and a monthly status, I need to make visuals such as a card with patients per sex, a matrix with patient names and age in months, etc... So far, to filter between who is and isn't admitted anymore, I check if a column called [Discharge] is blank. If there's text, then the patient has left and will no longer show up. This works for the daily version, but it doesn't work for the monthly version, because I'd be leaving out the people who were admitted on a 31st and haven't left. I later started experimenting with solutions I saw here and there and ended up making a chimera of a formula, but to no avail. To count different people on these visuals, I use the same population formula (Note: My database contains various areas, but I am only interested in the fields that say Utin (ICU)): Hospital-ICU_Population = CALCULATE(DISTINCTCOUNT(Hospital-ICU[Episode Number]), FILTER(Hospital-ICU,(Hospital-ICU[Area] == "Utin" || Hospital-ICU[Area] == "Utin*"))) My first approach towards solving this problem was making a new table based on the original data, called CALCULATETABLE(): HOSPITAL-ICU_LASTENTRY = CALCULATETABLE(HOSPITAL-ICU, FILTER(HOSPITAL-ICU,HOSPITAL-ICU[DISCHARGE] <> BLANK()) ) However, like I mentioned, this makes the numbers come out wrong as I'm not counting those who entered on the 31st and are still admitted. Then I tried the following formula that'd return True or False to the fact that that entry was the last entry. However, it returned everything as True... isLastEntry = CALCULATE( MAX(HOSPITAL-ICU[Revision Date]), FILTER(ALL(HOSPITAL-ICU), (HOSPITAL-ICU[Episode Number] = EARLIER(HOSPITAL-ICU[Episode Number]) Later on I added a chunk that I found online, and this seemed to return the right result for Population in the cards, but not for the Matrix, as the patients would show up repeated and their data would sum (And quite frankly, I can't find sense to why it helped). isLastEntry = VAR __Max = MAXX( FILTER( HOSPITAL-ICU, MONTH([Revision Date]) = MONTH(EARLIER([Revision Date])) && YEAR([Revision Date]) = YEAR(EARLIER([Revision Date])) ), [Revision Date] ) RETURN CALCULATE( MAX(HOSPITAL-ICU[Revision Date]), FILTER(ALL(HOSPITAL-ICU), (HOSPITAL-ICU[Episode Number] = EARLIER(HOSPITAL-ICU[Episode Number]) && HOSPITAL-ICU[Revision Date] == __Max))) And that's pretty much it. I am stumped on what other kind of filter or formula to use, as I can't find anything I can think about using on Power BI's documentation, as there is an EARLIER() formula, but there isn't a LATEST() formula. Any help and tips are appreciated. Here I'll add a portion of my data, along with extra info. Here's some sample data My expected result is to see 12 different patients in the month of May that were in the Utin area. 6 men and 6 women. If the data on [Area] has an asterisk, it means there was a change, but the patient is still in this area. Similarly, there are . on [Discharge], these just indicate a change of Area as well, but I just remove them with Power Query so they won't interfere.Solved1.3KViews0likes4Comments