Forum Discussion

jmkvalsund's avatar
jmkvalsund
Icon for Helper III rankHelper III
2 years ago
Solved

Measure showing running difference in table visual?

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



Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

     

  • jmkvalsund's avatar
    jmkvalsund
    Icon for Helper III rankHelper III
    2 years ago

    Hi Anonymous ,

    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        jmkvalsund
        Icon for Helper III rankHelper III
        2 years ago

        Hi Anonymous ,

         

        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

  • jmkvalsund's avatar
    jmkvalsund
    Icon for Helper III rankHelper III
    2 years ago

    Anonymous    

    Thanks a lot, works great!!

     

    Regards,

    John Martin