Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Excel PT functionallity in Power BI : Show Values As - Difference From

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 calcu...
Need Help
DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
3 years ago

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