How to retrieve last date non-zero value for each category

[AlpanaSi]

Hello

I am trying to resolve a problem since long. Below is a table with sub-categories like revenue from X, Y,...Product. Targets are set progressively over future time period for the product to achieve this revenue... here it is for next 3 years (2022-2024). As the time progresses, actual value will be entered. For this scenario, lets consider we are currently in 2023 and all the actual achieved value till 2023 will be entered and beyond that it is 0 because it lies in the future years and will entered subsequently as the time progress. 

Given the content, what I want is to build a gauge meter, line chart and KPI card.

On gauge meter, I want to show actual value, target value and planned value. E.g. for Revenue from X Product - Actual value = 11.32, Target value = 13.32 and Planned Value = 40

Similarly, on line chart I want to show line charts of Actual and Target, wherein target will be shown for all dates and actual will be shown for only those dates where value is not 0

KPI Card should return the value of actual, target and planned in one card.

The help that I need is writing code in how to return last date values (both actual and target) for each sub-category and return non-zero values for actual. Please note, the actual and target values might not be always in increasing order in real business scenario, so max function will not work here.

Please help in writing code for the same.   

Category	Sub-Cateogory	Date	Target	Actual
Financials	Revenue from X Product	11/1/2022	0	0
Financials	Revenue from X Product	11/1/2023	13.32	11.32
Financials	Revenue from X Product	11/1/2024	26.68	0
Financials	Revenue from X Product	11/1/2025	40	0
Financials	Revenue from X Product	11/1/2022	12	10
Financials	Revenue from Y Product	2/1/2023	13.34	11.34
Financials	Revenue from Y Product	5/1/2023	14.64	12.64
Financials	Revenue from Y Product	8/1/2023	15.99	13.99
Financials	Revenue from Y Product	11/1/2023	17.33	15.33
Financials	Revenue from Y Product	2/1/2024	18.67	0
Financials	Revenue from Y Product	5/1/2024	19.99	0
Financials	Revenue from Y Product	8/1/2024	21.33	0
Financials	Revenue from Y Product	11/1/2024	22.67	0
Financials	Revenue from Y Product	3/1/2025	24.42	0
Financials	Revenue from Y Product	6/1/2025	25.77	0
Financials	Revenue from Y Product	9/1/2025	27.11	0
Financials	Revenue from Y Product	12/1/2025	28.44	0

Thanks

[v-xiaotang · Accepted solution]

Hi @AlpanaSi 

Thanks for reaching out to us.

You can try these measures,

Actual_X = 
var _sub="Revenue from X Product"
var _year=2023 //lets consider we are currently in 2023 and all the actual achieved value till 2023 will be entered and beyond that it is 0
return CALCULATE(MAX('Table'[Actual]),FILTER(ALL('Table'),'Table'[Sub-Cateogory]=_sub && 'Table'[Date].[Year]=_year))

Target_X = 
var _sub="Revenue from X Product"
var _year=2023 //lets consider we are currently in 2023 and all the actual achieved value till 2023 will be entered and beyond that it is 0
return CALCULATE(MAX('Table'[Target]),FILTER(ALL('Table'),'Table'[Sub-Cateogory]=_sub && 'Table'[Date].[Year]=_year))

Planned_X = 
var _sub="Revenue from X Product"
var _maxDate=MAXX(FILTER(ALL('Table'),'Table'[Sub-Cateogory]=_sub),[Date])
return CALCULATE(MAX('Table'[Target]),FILTER(ALL('Table'),'Table'[Sub-Cateogory]=_sub && 'Table'[Date]=_maxDate))

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[nvprasad]

Hi AlpanaSi,

Cand you try with below DAX?

Target NonZero LatestDt = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Subcategory]),'Table'[Target]>0)

Actual NonZero LatestDt = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Subcategory]),'Table'[Actual]>0)

Appreciate a Kudos! ‌‌
If this helps and resolves the issue, please mark it as a Solution! ‌‌

Regards,
N V Durga Prasad

[AlpanaSi]

Hello @nvprasad 

I dont want to retrieve the last date, instead retreive non-zero actual and target values corresponding to last date. For e.g.

For Revenue from X Product, Actual value = 11.32, Target value = 13.32 and Planned Value = 40. I want to use these values to build gauge meter and KPI card for each subcategory.