cancel
Showing results for
Did you mean:
Super User

## NEED HELP!!!Get latest value by customer

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'

Proud to be a Super User!

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

Proud to be a Super User!

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

Proud to be a Super User!

Microsoft

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

@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.

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

Proud to be a Super User!

Super User
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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Super User

@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

Proud to be a Super User!

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors