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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Xandman
Helper II
Helper II

Finding latest record for any month

Hi

 

I receive total reports everyday instead of "newly added item" reports.

 

For one dashboard, I've managed to use power query to only show the latest file. But now I need to include all records but only display the latest record for each month.

 

The table is similar to this:

Capture.PNG

 

Hope someone can help.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Xandman , Try a measure like

 


Measure =
VAR __id = MAX ('Table'[product] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[product] = __id )
CALCULATE ( sum ('Table'[Qty] ), VALUES ('Table'[product] ),'Table'[product] = __id,'Table'[Date] = __date )

View solution in original post

8 REPLIES 8
Xandman
Helper II
Helper II

Hi 

 

Ive attached a sample pbix file. Hope someone can guide me to how to fix the measure.

 

pbix file 

Xandman
Helper II
Helper II

To add I am planning to display MoM results too by comparing the latest record of a month and also use a line graph to show each month's total results based on the latest records submitted for the month.

Xandman
Helper II
Helper II

Hi I tried to follow but I can't figure out the error.

 

LastestResults =
Var packageid = MAX(AllDateLOCCount[Franchise])
var lastestdate = CALCULATE(MAX(AllDateLOCCount[Effective Date]), ALLSELECTED(AllDateLOCCount), AllDateLOCCount[Franchise] = packageid)
CALCULATE (SUM(AllDateLOCCount[Active], VALUES (AllDateLOCCount[Franchise]), AllDateLOCCount[Franchise] = packageid, AllDateLOCCount[Effective Date] = lastestdate)
 
It says "syntax for CALCULATE is incorrect

I'm not sure if correct but I added return to the formula:

 

LastestResults =
Var packageid = MAX(AllDateLOCCount[Franchise])
var lastestdate = CALCULATE(MAX(AllDateLOCCount[Effective Date]), ALLSELECTED(AllDateLOCCount), AllDateLOCCount[Franchise] = packageid)
return CALCULATE (SUM(AllDateLOCCount[Active], VALUES (AllDateLOCCount[Franchise]), AllDateLOCCount[Franchise] = packageid, AllDateLOCCount[Effective Date] = lastestdate)
 
Then it was accepted but when I placed in them in a table, it was empty.

@Xandman , Try like

 

LastestResults =
Var packageid = MAX(AllDateLOCCount[Franchise])
var lastestdate = CALCULATE(MAX(AllDateLOCCount[Effective Date]), ALLSELECTED(AllDateLOCCount), AllDateLOCCount[Franchise] = packageid)
CALCULATE (SUM(AllDateLOCCount[Active]), VALUES (AllDateLOCCount[Franchise]), AllDateLOCCount[Franchise] = packageid, AllDateLOCCount[Effective Date] = lastestdate)

 

a parenthesis was missing

Hi sorry, I didnt see your reply. I pasted the formula you gave to the measure but I get the error unexpected Calculate expression.

amitchandak
Super User
Super User

@Xandman , Try a measure like

 


Measure =
VAR __id = MAX ('Table'[product] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[product] = __id )
CALCULATE ( sum ('Table'[Qty] ), VALUES ('Table'[product] ),'Table'[product] = __id,'Table'[Date] = __date )

Hi @amitchandak 

 

Accepted this as a solution, I finally figured out why it wasn't working. I created a table instead of a measure for your solution. 

 

Though, I am still looking for a way to determine the latest file of each month. Your solution returns the latest record of a selected month which is also what I wanted for a table connected to a slicer. I just need to figure out what to add to your formula that it filters by each month so I can have a bar chart showing all months of the year.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Kudoed Authors