NEED HELP!!!Get latest value by customer

[ryan_mayu]

Hi all,

Below is my sample data.

name	amount	date
a	100	1/1/2018
a	200	2/1/2018
a	300	3/1/2018
b	350	1/1/2018
b	400	2/1/2018
b	500	3/1/2018
b	600	4/1/2018
c	700	1/1/2018
c	800	2/1/2018
c	900	3/1/2018
c	1000	4/1/2018

Here is our requests. We want to calculate MOM by customer. If the month or date filter selected all or nothing. The MOM = Latest month - last month.

Below are the codings I used.

LATEST AMOUNT = 
VAR MAXDATE= MAX('fact1'[date])
return CALCULATE(SUM(fact1[amount]),FILTER(fact1,'fact1'[date]=MAXDATE))

LATEST AMOUNT = 
VAR MAXDATE= MAX('fact1'[date])
return CALCULATE(SUM(fact1[amount]),FILTER(fact1,'fact1'[date]=MAXDATE))

MOM = [LATEST AMOUNT]-[PM AMOUNT]

Now I encountered some issues. The result is correct for b and c. However, it retreived data last month and two months ago.

I want to know how to change the coding and show a as '0' and '300. The correct MOM for a should be '-300'

Thanks in advance.

[ryan_mayu · Accepted solution]

Hi all, I figure this out. I have to choose the max date in all table VAR MAXDATE= MAXX(all('fact','fact1'[date]).

[v-cherch-msft]

Hi @ryan_mayu

It seems you've solved it, right? If so,please accept the helpful solution to close this thread.

Regards,

Cherie

[ryan_mayu]

@v-cherch-msft

Could you please take a look at another similar issue? Acutally, my real data is not as simple as what I did in the test.

So I still have other questions about this kind of topic

Thanks in advance.

https://community.powerbi.com/t5/Desktop/URGENT-need-help-to-calculate-the-value-of-max-date/m-p/595...

[Greg_Deckler]

Looks like a measure totals issue, see this Quick Measure: https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[ryan_mayu]

@Greg_Deckler

Thanks for your help. I think my issue is abou tthe maxdate select. For b and c , maxdate is 2018/4/1, the maxdate for a is 3/1/2018. Could you please help me on how to change the maxdate for a is also 4/1/2018. Then the value for a is blank() or 0.

Thanks

Ryan