CODEWITHSUNDEEP
powerbidaxcalculated-columns
Andy
Andy

Reputation: 85

Trying to Calculate Last Month Values on Power BI

I have a FinalRawData table with invoices for certain IDs (circuits) month to month. The values are stored in the column Total (USD). The dates are stored in the Invoice Date field or on another table as a Calendar[Date] value.

I need to calculate variance, so if it is more than 10% or $1000 compared to the previous month's invoice (not necessarily consecutive months), I need the variance value, either negative or positive. If not, I want it to be 0.

I have no idea why this formula is returning the current month's value instead of the difference against the previous one.

Amount_Difference =
    
    VAR CurrentRowDate = MAX('finalrawdata'[Invoice Date])
    
    VAR CurrentRowItem = MAX('finalrawdata'[Item])
    
    VAR CurrentRowAmount = MAX('finalrawdata'[Total (USD)])
    
    VAR PreviousMonthAmount =
    
    CALCULATE(
    
         SUM('finalrawdata'[Total (USD)]),
    
         'finalrawdata'[Invoice Date] = EOMONTH(CurrentRowDate, -1),
    
         'finalrawdata'[Item] = CurrentRowItem
    
    )
    
    VAR AmountDiff = CurrentRowAmount - PreviousMonthAmount
    
  RETURN

    IF(
    
      ABS(AmountDiff) > 1000 || ABS(AmountDiff) > CurrentRowAmount * 0.1,
    
      AmountDiff,
    
      BLANK()
    )

Upvotes: 0

Views: 82

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12111

The PreviousMonthAmount isn't returning a value hence the result you are getting. A couple of obstacles:

  • PreviousMonthAmount has row context and so it will only see itself - you will need one of the Filter functions to get the other rows.
  • You mentioned that the months may not be consecutive - so EOMONTH(CurrentRowDate, -1) won't work here.

Try the following:

Amount_Difference = 
    
    VAR CurrentRowDate = [Invoice Date]
    
    VAR CurrentRowAmount = [Total (USD)]
    
    VAR PreviousMonthAmount =
    
    CALCULATE(
    
         MIN('finalrawdata'[Total (USD)]),
    
         TOPN(1,
           FILTER(
            ALLEXCEPT('finalrawdata', 'finalrawdata'[Item]), 
            'finalrawdata'[Invoice Date] < CurrentRowDate
           ), 
           [Invoice Date], DESC
         )    
    )
    
    VAR AmountDiff = IF(NOT ISBLANK(PreviousMonthAmount), CurrentRowAmount - PreviousMonthAmount)
    
  RETURN

    IF(
    
      ABS(AmountDiff) > 1000 || ABS(AmountDiff) > CurrentRowAmount * 0.1,
    
      AmountDiff,
    
      BLANK()
    )

The above gets the previous invoice value based on date.

ALLEXCEPT('finalrawdata', 'finalrawdata'[Item]) gets all rows for the Item.

Upvotes: 0

Related Questions