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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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 )

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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 )

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.