March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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.
Solved! Go to 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 )
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.
Hi I tried to follow but I can't figure out the error.
I'm not sure if correct but I added return to the formula:
@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.
@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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
124 | |
89 | |
84 | |
70 | |
51 |
User | Count |
---|---|
206 | |
143 | |
97 | |
79 | |
68 |