difference measure in chart gives false results

[ioannagr]

In a KPI i have this difference to get the sum of this expression for the max(visitdate) of all dataset 

sum( {<[visitdate]={ '$(= max([visitdate] ))' },[availability]={'available'} >} [amount purchased]) - sum( { < WeekDay = {'$(= weekday( max(date([visitdate] ),'DD/MM/YYYY')))'} , Week= {'$(=week(max([visitdate] )))'}, Year = {'$(=year(max([visitdate] ))-1)'},[availability]={'available'} > } [amount purchased] )

When I put this difference as a measure in a chart with dimension each Client name that has different max visit dates, so a lot haven't visited on the total max date , I get screwed up results for the difference UNLESS i click on a certain client in the chart and then the numbers are correct like the KPI (that then takes into account their max visit date).

What i mean is: when i select a client from the filter Client, the kpi returns correct results.

How do i fix this?

[sunny_talwar]

@ioannagr set analysis is evaluated at the chart level and not by dimension. What is that if you use Max(Date) it will be the max date across the board and not by client. In order to do it by dimension, you will need to use FirstSortedValue function with Aggr() to do this.

May be try this for numerator

Sum(Aggr(If([visitdate] = Max(TOTAL <[Client Name]> [visitdate]), Sum({<[availability] = {'available'}>} [amount purchased]), [Client Name], [visitdate]))

not entirely sure what the goal for denominator is.

[ioannagr]

@sunny_talwar hi, i will try it just now and let you know.

I want the sum for the max date for each client and the same date the previous year.

[ioannagr]

@sunny_talwar  gives error in expression that a ) or } is missing 

[sunny_talwar]

@ioannagr Would you be able to share a sample to show this?

[sunny_talwar · Accepted solution]

@ioannagr I see the issue, try this

Sum(Aggr(If([visitdate] = Max(TOTAL <[Client Name]> [visitdate]), Sum({<[availability] = {'available'}>} [amount purchased])), [Client Name], [visitdate]))

[ioannagr]

@sunny_talwar  yes it's working! But firstly I have to understand what this script means.

For example, how can I convert this for the same date, previous year?

Say that one Client has visit date 6/10/2020 and purchased x available things.

I want to know for this Client on 6/10/2019 the amount y they purchased of available things 

and get the difference x-y. 

[ioannagr]

@sunny_talwar sorry for directly calling for you help but since you helped me on the first one i think you have the answer to this one too that i did for the same date last year:

Sum(Aggr(If([visitdate] = Max(TOTAL <[client name]> Date(AddYears([visitdate],-1))), Sum({<[availability] = {'available'}>} [amount purchased])), [client name], [visitdate]))

gives me wrong results . How should i treat it?

[sunny_talwar]

May be this

Sum(Aggr(If([visitdate] = AddYears(Max(TOTAL <[client name]> [visitdate]), -1), Sum({<[availability] = {'available'}>} [amount purchased])), [client name], [visitdate]))

[ioannagr]

@sunny_talwar  This gives me some numbers per client but i don't know if it's correct because the same differences in my  KPI when i select a random client from the Filter "Client" gives other results

MY kpi

sum( {<[visitdate]={ '$(= max([visitdate] ))' },[availability]={'available'} >} [amount purchaesed])-sum( { < WeekDay = {'$(= weekday( max(date([visitdate] ),'DD/MM/YYYY')))'} , Week = {'$(=week(max([visitdate])))'}, Year = {'$(=year(max([visitdate]))-1)'},[availability]={'available'} > } [amount purchased] )

The nominator gives same results in both situations, but the denominators differ so i don't know which is correct, what i did or what you did  . Help pleeeeeeeeease 🙂