How to calculate lowest score per person per month/year without creating many extra tables?

[PunchBird]

Hello all,

I have a nice brain cruncher.

I have a table that looks like this, but then with 1000+ rows:

Name    Date           Score
Jack    01/01/2021   2
Jill       02/01/2021   3
Joe     01/01/2021   1
Jack    09/01/2021   1
Jill       10/01/2021   2
Joe      12/01/2021   1
Joe      03/02/2021   1
Jill       18/02/2021   3
Joe      19/02/2021  1
Jill        02/03/2021  2
Jack     01/03/2021  2

I want to create a heat map that displays the lowest score per person per month/year, so the values I need would be:
Name Month/Year Score
Jack    01/2021      1
Jack    02/2021      1 -> please note there was no value for 02/2021 in the original table, so here the value of 01/2021 is downfilled
Jack    03/2021      2
Jill      01/2021       2
Jill      02/2021       2
Jill      03/2021       1
etc.

I found a way of doing this, but it's cumbersome and creates many extra tables. So what I do now is:

- I create 3 new tables out of the existing table, filtered on each name. So based on the example above the resulting table for Jack is:

Name    Date           Score
Jack    01/01/2021   2
Jack    09/01/2021   1
Jack     01/03/2021  2

- Then from each new table I create another table that contains all dates of the calendar, with the dates with empty score values downfilled with the last nonblank.

- Then in the visual I filter on minimum value for each month/year.

Phew, lots of extra tables! My question: is there a way of getting the values desired without having to create so many extra tables? Ideally I would want one DAX measure per person, instead of 2 extra tables per person.
(Please note I am not asking how to create a heat map, I just want to know a better way to create the values)

Anyone any ideas?

[parry2k · Accepted solution]

@PunchBird they are not, here is the difference:

First measure has date granularity -> LASTNONBLANKVALUE ( 'Calendar'[Date], MIN ( 'Table'[Score] ) )

2nd measure has month granularity -> LASTNONBLANKVALUE ( 'Calendar'[Month], MIN ( 'Table'[Score] ) )

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[PunchBird]

@parry2k ah great, the devil is in the detail 😉. Thanks so much for your help, very much appreciated!

[parry2k]

@PunchBird page 2 in attached has the solution and there a table called Table  in the model and also the Calendar table, ignore other tables

[parry2k]

@PunchBird tweak the measure as below and I think this will do it:

CALCULATE ( 
    LASTNONBLANKVALUE ( 'Calendar'[Month], MIN ( 'Table'[Score] ) ),  
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ) 
) 

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[PunchBird]

@parry2k Thanks so much!! I looked at the tweaked measure and compared it to the previous one you provided but they both seem identical?

[parry2k]

@PunchBird here is the simple measure and make sure you have a calendar table in your model and have a relationship set to your table on the date column, you can create a table following my blog post here Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio...

CALCULATE ( 
    LASTNONBLANKVALUE ( 'Calendar'[Date], MIN ( 'Table'[Score] ) ),  
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ) 
) 

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[PunchBird]

@parry2k thanks this measure looks good and is almost correct. In a month/year where the score changes the measure takes the score added that month, also when it's higher than the previous score. So if Janet's score is 1 until 10 May 2021 and then 2 from 11 May 2021, this measure takes 2 but it should take 1 for May 2021 because that was the lowest score. Any idea how to adjust that?

[mahoneypat]

Here is a measure expression that I believe does what you are looking for.  Note that a separate Date table is needed to avoid auto-exist issues (and it's a good practice).

Latest Min Per Month =
VAR thismonthmax =
    MAX ( 'Date'[Date] )
VAR months =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( Scores, 'Date'[YearMonthShort], Scores[Name] ),
            "cMaxDate", CALCULATE ( MAX ( 'Date'[Date] ) ),
            "cMin", CALCULATE ( MIN ( Scores[Score] ) )
        ),
        ALL ( 'Date' ),
        'Date'[Date] <= thismonthmax
    )
VAR maxdatevalue =
    MAXX ( FILTER ( months, [cMin] > 0 ), [cMaxDate] )
VAR result =
    MAXX ( FILTER ( months, [cMaxDate] = maxdatevalue ), [cMin] )
RETURN
    result

Pat

[PunchBird]

@mahoneypat thanks! I tested your measure. It does filter per user but what it does is put the lowest score for the entire calendar in all month/years instead of the lowest score per month/year. 

[mahoneypat]

Oh yeah.  Forgot about that.  Please try this version instead.  This one only looks backward if the current month doesn't have a min value.

Latest Min Per Month =
VAR thismonthmin =
    MIN ( Scores[Score] )
VAR thismonthmax =
    MAX ( 'Date'[Date] )
VAR months =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( Scores, 'Date'[YearMonthShort], Scores[Name] ),
            "cMaxDate", CALCULATE ( MAX ( 'Date'[Date] ) ),
            "cMin", CALCULATE ( MIN ( Scores[Score] ) )
        ),
        ALL ( 'Date' ),
        'Date'[Date] <= thismonthmax
    )
VAR maxdatevalue =
    MAXX ( FILTER ( months, [cMin] > 0 ), [cMaxDate] )
VAR result =
    MAXX ( FILTER ( months, [cMaxDate] = maxdatevalue ), [cMin] )
RETURN
    IF ( ISBLANK ( thismonthmin ), result, thismonthmin )

Pat

[Greg_Deckler]

@PunchBird Create a column in your original table:

YearMonthColumn = FORMAT(MONTH([Date]),"00") & "/" & YEAR([Date])

Create this measure:

MinimumMeasure = MIN('Table'[Score])

Create a Matrix visualization with [Name] in Rows, [YearMonthColumn] in Columns and MinimumMeasure in Values.

[PunchBird]

@Greg_Deckler thanks but this doesn't cater for months that are missing in the original table, and I need all months