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 have an Access DB which feed data into ane Excel Data Model and Power BI. This is the weekly reporting cash balances of a couple companies.
In Excel is quite easy to do the calculation I want: "Show Values As", "Difference From" "Previous".
And VOILA!! I've the calculculation i want, which is the difference between the current and the previos column (reporting date):
How is this done in Power BI? I've tried some DAX with no success: CALCULATE + SAMEPERIODLASTYEAR or PARALELL or ALL , ETC.
In advance thanks for your support.
Oscar
As a thought experiment, I created the measure below that mimics the "Show Values as Difference From" option in a pivot table. Normally, date calculations are performed on a date table. But, if you're looking for a quick "current vs. previous period", this might be of interest. It subtracts the previous amount from the current amount, whether the period is year, quarter, month, week, or day. The only table in this data model is FactTable.
Difference From =
VAR vDate =
MAX ( FactTable[Date] )
VAR vPreviousDate =
CALCULATE (
MAX ( FactTable[Date] ),
ALLSELECTED ( FactTable[Date] ),
FactTable[Date] < vDate
)
VAR vAmount = [Sum of Amount]
VAR vPreviousAmount =
CALCULATE ( [Sum of Amount], FactTable[Date] = vPreviousDate )
VAR vResult =
IF ( vPreviousDate <> BLANK (), vAmount - vPreviousAmount )
RETURN
vResult
Proud to be a Super User!
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 |
---|---|
106 | |
94 | |
76 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |