Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |