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.
Hello,
We have an Excel spreadsheet which we're hoping to migrate to Power BI. The spreadsheet has a formula which references the prior date's value - but that prior date's value is a formula. Please see Excel formula highlighted -- as well as the DAX I've come up with so far. I can reference prior day value from a table fairly easily - but not sure how to do that when that value is a measure. Greatly appreciate any help which can be offered.
TEST_PriorRecord = VAR EarliestRecordDate = CALCULATE(MIN('Dates (Filter)'[AsOfDate]),ALLSELECTED('Dates (Filter)')) VAR CurrentRecordDate = CALCULATE(MAX('Dates (Filter)'[AsOfDate])) VAR PriorDate = CALCULATE(MAX(Positions[AsOfDate (Filter)]),TOPN(1,FILTER(ALLSELECTED('Dates (Filter)'[AsOfDate]),'Dates (Filter)'[AsOfDate]<MAX('Dates (Filter)'[AsOfDate])),'Dates (Filter)'[AsOfDate],DESC)) VAR PriorDateValue = CALCULATE(SUM(AccountReturns[Account_ReportedReturn_QTD]),'Dates (Filter)'[AsOfDate]=PriorDate) VAR IsEarliestRecord = IF(EarliestRecordDate = CurrentRecordDate, TRUE, FALSE) RETURN IF(IsEarliestRecord, 1, PriorDateValue)
Thanks,
Dan
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |