Measure showing running difference in table visual?

[jmkvalsund]

Hi,

does anyone know how to create a masure that shows the running difference of another column (which is a measure) in a table visual)?
I have a two measures like:

M_Sum_Sales = SUM('tblSales'[SaleAmount])

M_Sum_Selected_Seller = CALCULATE('tblSales'[M_Sum_Sales] ,FILTER('tblSales','tblSales'[Seller]=[M_SelectedSeller]))+0

M_SelectedSeller comes from a slicer.

Each line in tblSales has a DateID, and M_Sum_Selected_Seller is calculated and grouped by DateID, so I get the selected seller's accumulated sales for each DateID, works fine.

Now, how can I add a column to the table visual that for each DateID calculates the difference between the current M_Sum_Selected_Seller and the M_Sum_Selected_Seller on the previous line (DateID)?  Thought this should be easy, but I don't get it. Tried using the EARLIER function, but that was not allowed. 
Anyone?

Regards,
John Martin

[jmkvalsund]

@v-heq-msft    

Thanks a lot, works great!!

Regards,

John Martin

[jmkvalsund]

Hi @v-heq-msft ,

heres a fictive table created in Excel to show what I want to achieve. Data table to the left and the wanted output to the right.

Note:
The DateID is End-Of-Month for all previous months, since the Saleamount is accumulated by customerID (not shown here since it is not of importance). For current month, DateID is the latest update to PowerBI, typically two days before current date.

My headache is to create the column showing "Diff previous month".  Which is just the difference in M_Sum_Selected_Seller for each DateID.

Regards,

John Martin

[v-heq-msft · Accepted solution]

Hi @jmkvalsund ,
You can try this measure

Diff = 
VAR CurrentDate = MAX('Table'[DateID])
VAR PreviousDate = 
    CALCULATE(
        MAX('Table'[DateID]),
        FILTER(
            ALL('Table'),
            'Table'[DateID] < CurrentDate
        )
    )
VAR PreviousSale = 
    CALCULATE(
        SUM('Table'[SaleAmount]),
        'Table'[DateID] = PreviousDate
    )
RETURN
IF(
    ISBLANK(PreviousSale),
    0,
    SUM('Table'[SaleAmount]) - PreviousSale
)

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jmkvalsund]

Hi @v-heq-msft ,

I'm feeling a bit stupid now, but could you show me how to create another measure which accumulates the values from Diff for each DateID? 

Regards,
John Martin

[v-heq-msft]

Hi @jmkvalsund ,

Can you provide some sample data? We can better understand the problem and help you.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly