Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ryan_mayu
Super User
Super User

NEED HELP!!!Get latest value by customer

Hi all,

 

Below is my sample data.

 

nameamountdate
a1001/1/2018
a2002/1/2018
a3003/1/2018
b3501/1/2018
b4002/1/2018
b5003/1/2018
b6004/1/2018
c7001/1/2018
c8002/1/2018
c9003/1/2018
c10004/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.
c1.JPG
 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




1 ACCEPTED SOLUTION
ryan_mayu
Super User
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]).




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
ryan_mayu
Super User
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]).




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @ryan_mayu

 

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

 

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.

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Greg_Deckler
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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.