Max value with a subcategory

[lauriemclolo]

Hello.

I have data that looks like the data inserted below.

I am trying to sum the enrollment on the 2nd largest begin date for each ag_level.

For example (using the data sample below), for ag_level=Daily, the Max begin date is 3/10 so the 2nd greatest begin date is 3/9.

However, for ag_level=WEEKLY, the max begin date is 3/7/2021, so the 2nd greatest begin date is 2/28/2021.

However, to clarify, I want a separate measure for each ag level.  That is, for ag_level=WEEKLY, I want a  "last week" enrollment as one measure.  I would like to have a "yesterday" enrollment for ag_level=DAILY.   Can you help?

[ryan_mayu · Accepted solution]

@lauriemclolo 

you can create a calculated column

column=

var _max=CALCULATE(max('Table'[begin date]),ALLEXCEPT('Table','Table'[ag_level]))

return if('Table'[begin date]=maxx(FILTER('Table','Table'[ag_level]=EARLIER('Table'[ag_level])&&'Table'[begin date]<_max),'Table'[begin date]),1)

filter the column to 1, then no matter you choose daily or weekly , it will only show the data that you want.

[v-agajavelly]

Hi @lauriemclolo ,

I hope the response provided helped in resolving the issue. If you still have any questions, please let us know we are happy to address.

Thanks,
Akhil.

[v-agajavelly]

Hi @lauriemclolo ,

Just checking in were you able to test danextian’s DAX measures for the 2nd latest Daily and Weekly enrollments?
Curious to know if they’re now returning the correct “Yesterday” and “Last Week” results in your visuals.

Thanks,
Akhil.

[v-agajavelly]

Hi @lauriemclolo .

Thanks @danextian  for jumping in with the detailed solution using INDEX and PARTITIONBY. That aligns perfectly with what they asked for having separate measures for Daily and Weekly that always return the 2nd latest date enrollment totals, even outside of a table visual.

@lauriemclolo  were you able to try out those measures in your report and confirm if they give you the expected “Yesterday” and “Last Week” values?

Thanks,
Akhil.

[v-agajavelly]

Hi @lauriemclolo ,

Thanks @Ashish_Mathur , @ryan_mayu , @Irwan and @danextian  for your helpful inputs. @lauriemclolo  did you get a chance to review the solution shared above? If you’re still facing any issues, please let us know.

Thanks,
Akhil.

[danextian · Accepted solution]

Hi @lauriemclolo 

Try the following measures:

All Second Highest Dates = 
VAR _perPartition =
    SUMX (
        VALUES ( 'Table'[ag_level] ),
        VAR _secondDate =
            MAXX (
                INDEX (
                    2,
                    ALL ( 'Table'[ag_level], 'Table'[begin date] ),
                    ORDERBY ( [begin date], DESC ),
                    ,
                    PARTITIONBY ( 'Table'[ag_level] )
                ),
                [begin date]
            )
        RETURN
            SUMX (
                FILTER ( 'Table', 'Table'[begin date] = _secondDate ),
                'Table'[enrolled_count]
            )
    )
RETURN
    _perPartition

Daily Second Highest Dates = 
VAR _perPartition =
    SUMX (
        VALUES ( 'Table'[ag_level] ),
        VAR _secondDate =
            MAXX (
                INDEX (
                    2,
                    ALL ( 'Table'[ag_level], 'Table'[begin date] ),
                    ORDERBY ( [begin date], DESC ),
                    ,
                    PARTITIONBY ( 'Table'[ag_level] )
                ),
                [begin date]
            )
        RETURN
            SUMX (
                FILTER ( 'Table', 'Table'[begin date] = _secondDate && 'Table'[ag_level] = "Daily" ),
                'Table'[enrolled_count]
            )
    )
RETURN
    _perPartition

Weekly Second Highest Dates = 
VAR _perPartition =
    SUMX (
        VALUES ( 'Table'[ag_level] ),
        VAR _secondDate =
            MAXX (
                INDEX (
                    2,
                    ALL ( 'Table'[ag_level], 'Table'[begin date] ),
                    ORDERBY ( [begin date], DESC ),
                    ,
                    PARTITIONBY ( 'Table'[ag_level] )
                ),
                [begin date]
            )
        RETURN
            SUMX (
                FILTER ( 'Table', 'Table'[begin date] = _secondDate && 'Table'[ag_level] = "Weekly" ),
                'Table'[enrolled_count]
            )
    )
RETURN
    _perPartition

Details are in the attached pbix

[Ashish_Mathur]

Hi,

Based on the table that you have shared, show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[ryan_mayu · Accepted solution]

@lauriemclolo 

you can create a calculated column

column=

var _max=CALCULATE(max('Table'[begin date]),ALLEXCEPT('Table','Table'[ag_level]))

return if('Table'[begin date]=maxx(FILTER('Table','Table'[ag_level]=EARLIER('Table'[ag_level])&&'Table'[begin date]<_max),'Table'[begin date]),1)

filter the column to 1, then no matter you choose daily or weekly , it will only show the data that you want.

[lauriemclolo]

Hi.  Thank you.  I may be able to use this in another dashboard, but my current one needs to have the weekly and daily separated so I can show both simultaneously in the view, as shown by @Irwan  below .  However, I'm curious, because I did try to build your formula (for use later), but the portion 'Table'[begin date]= will not accept the column "begin date".  It appears to want a measure and not a column in that spot of the code.

[ryan_mayu]

what's the expected output? only display the date? could you pls clarify this?

[Irwan]

hello @lauriemclolo 

please check if this accomodate your need.

create two measures for [Yesterday] and [Last Week]

Yesterday =
var _MaxDate = 
CALCULATE(
    MAX('Table'[begin date]),
    ALLEXCEPT('Table','Table'[ag_level])
)
var _Yesterday = 
CALCULATE(
    SUM('Table'[enrolled_count]),
    FILTER(
        ALL('Table'),
        'Table'[ag_level]="Daily"&&
        'Table'[begin date]=_MaxDate-1
    )
)
Return
IF(
    SELECTEDVALUE('Table'[ag_level])="Daily"&&MAX('Table'[begin date])=_MaxDate,
    _Yesterday
)

Last Week =
var _MaxDate = 
CALCULATE(
    MAX('Table'[begin date]),
    ALLEXCEPT('Table','Table'[ag_level])
)
var _MaxWeek = WEEKNUM(_MaxDate)
var _Format = YEAR(SELECTEDVALUE('Table'[begin date]))&FORMAT(_MaxWeek,"00")
var _LastFormat = YEAR(SELECTEDVALUE('Table'[begin date]))&FORMAT(_MaxWeek-1,"00")
var _LastWeek = 
CALCULATE(
    SUM('Table'[enrolled_count]),
    FILTER(
        ALL('Table'),
        'Table'[ag_level]="Weekly"&&
        YEAR('Table'[begin date])&FORMAT(WEEKNUM('Table'[begin date]),"00")=_LastFormat
    )
)
Return
IF(
    SELECTEDVALUE('Table'[ag_level])="Weekly"&&YEAR(SELECTEDVALUE('Table'[begin date]))&FORMAT(WEEKNUM(SELECTEDVALUE('Table'[begin date])),"00")=_Format,
    _LastWeek
)

that _Format is for preventing wrong value when changing year (from week 52 to week 1)

Hope this will help.

Thank you.

[lauriemclolo]

Thank you @Irwan .  I see that this works when I review your pbix file.  However, it appears to be dependent on including rows in the visual.  That is, if I only want to show the value of "yesterday" (and not show any other fields), it displays as empty.  Is that because it uses "selected value" ?  I was trying to figure out how "selected value" comes into play.  Thank you again!

[Irwan]

hello @lauriemclolo 

since measure is based on filter context, then to make measure work you need a filter.

as you mentioned, when you all fields, the filter is no longer there to make the measure worked. As the result, your display is empty or blank.

selectedvalue only for conditional if, so the value is separated between DAILY and WEEKLY.

if you want show Yesterday and Last Week without any filter, then you need to make Yesterday and Last Week as a calculated column.

Hope this will help.

Thank you.