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 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

Need Help

1 Reply

  • DataInsights's avatar
    DataInsights
    Super 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