Finding latest record for any month

[Xandman]

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:

Hope someone can help.

[Xandman]

Hi 

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

pbix file 

[Xandman]

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]

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

[Xandman]

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.

[amitchandak]

@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

[Xandman]

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 · Accepted solution]

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

[Xandman]

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.