Forum Discussion
Power ParticipantRetrieving Values from Single Column
I have realised my mistake and deleted that post. I had also tried a solution as
Current Total = VAR _T = TOPN( 1, FILTER(CaseLoad,CaseLoad[Active Caseload]<>BLANK()), MONTH(CaseLoad[Month Year]),DESC) RETURN MAXX(_T,CaseLoad[Active Caseload])Previous Total = VAR _T = TOPN( 2, FILTER(CaseLoad,CaseLoad[Active Caseload]<>BLANK()), MONTH(CaseLoad[Month Year]),DESC) VAR _previous_date = MINX(_T,CaseLoad[Month Year]) RETURN CALCULATE(SUM(CaseLoad[Active Caseload]),CaseLoad[Month Year]=_previous_date)Is this a right approach.
Memorable MemberHi ArchStanton,
Assuming Month is a proper Date column and you have one row per month, I would identify the latest two non-blank dates first rather than use FIRSTNONBLANK.
One small thing with the previous-value measure already posted: filtering to every date before the latest date and then taking MAX(Active Caseload) works with your sample because the values are increasing. If an older month had a higher caseload, it could return that instead of the immediately previous month.
I would use:
Current Total = VAR LatestDate = CALCULATE( MAX('Caseload'[Month]), FILTER( ALL('Caseload'), NOT ISBLANK('Caseload'[Active Caseload]) ) ) RETURN CALCULATE( MAX('Caseload'[Active Caseload]), 'Caseload'[Month] = LatestDate ) Previous Total = VAR LatestDate = CALCULATE( MAX('Caseload'[Month]), FILTER( ALL('Caseload'), NOT ISBLANK('Caseload'[Active Caseload]) ) ) VAR PreviousDate = CALCULATE( MAX('Caseload'[Month]), FILTER( ALL('Caseload'), 'Caseload'[Month] < LatestDate && NOT ISBLANK('Caseload'[Active Caseload]) ) ) RETURN CALCULATE( MAX('Caseload'[Active Caseload]), 'Caseload'[Month] = PreviousDate )With your example these return 198 and 178.
Then the comparison can simply be:
Caseload Change = [Current Total] - [Previous Total]and, if you want a triangle indicator:
Caseload Trend = SWITCH( TRUE(), [Caseload Change] > 0, UNICHAR(9650), [Caseload Change] < 0, UNICHAR(9660), UNICHAR(8212) )The current Power BI Card visual also supports reference labels and conditional formatting, so you could show the current value as the callout and the previous value/change underneath rather than needing separate visuals.
AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
Power ParticipantIf I wanted the Triangles coloured Red for Up and Green for down, what would be the best way to do that?
My guess is to create another measure and use the Triangle measure in an IF statement?