Filter per last entry, per month

[Anonymous]

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.

[daXtreme]

Hi there.

First, a piece of advice. You don't learn Power BI and especially DAX like "I later started experimenting with solutions I saw here and there and ended up making a chimera of a formula, but to no avail." Such an approach to learning DAX will lead you nowhere. DAX can't be learned this way. Sorry 😞 My advice is this. If you want to learn DAX, grab yourself a good book and study it. The best book by a long shot is the one written by Marco Russo and Alberto Ferrari, The Definitive Guide to DAX.

Second, would you please give us some (fake?) data to work with? You can create a data set and then place a link to it here. You can store the file on any online shared drive, for instance Google Drive or OneDrive.

Thanks.

[Anonymous]

Thanks for the advice, I'll make sure to get an eBook version of that book.

As for your request, I did put up a link of fake data in the spoiler, does it not work?

[daXtreme · Accepted solution]

Please study the attached file carefully.

[daXtreme]

Ah... Sorry I did not notice the link. It's best to avoid spoilers 🙂