Forum Discussion
Max value with a subcategory
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?
Anonymous
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.Hi Anonymous
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 _perPartitionDaily 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 _perPartitionWeekly 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 _perPartitionDetails are in the attached pbix
12 Replies
Hi Anonymous
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 _perPartitionDaily 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 _perPartitionWeekly 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 _perPartitionDetails are in the attached pbix
hello Anonymous
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.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!
hello Anonymous
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.
Anonymous
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.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.
what's the expected output? only display the date? could you pls clarify this?
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.
Hi Anonymous ,
Thanks Ashish_Mathur , ryan_mayu , Irwan and danextian for your helpful inputs. Anonymous did you get a chance to review the solution shared above? If you’re still facing any issues, please let us know.
Thanks,
Akhil.Hi Anonymous .
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.
Anonymous 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.
