I need to compare dollar amounts by month from a base month

[daviddavidson]

I have lookeed nd not seen a solution to my problem.

I have payment amounts by month for several years.  I need to be able to set a filter to compare the dollar amounts for each month to the first month in the series.

For example if the month range is January 2020 to April 2020 and the data is like this

January          $100

February        $101

March             $102

April               $90

Ideally I need as output...

January          $100        Base amount

February        $1           $101-$100

March            $2           $102-$100

April              ($10)       $90-$100

But I will take January showing $0 instead of the base amount

The base month will change with the filter as will the number of months.

This is NOT the standard month to month change

Any help would be apreciated

[ImkeF]

Hi @daviddavidson ,
sorry, I cannot follow and will drop out here.
Hopefuly someone else will pick this up, otherwise I would recommend to open a new thread with for your requirement.

[daviddavidson]

Imke,

Thanks I think this is the right direction.  I realized the example I gave was too simplistic.

I have years of data for telecom charges with an accounting date.

The Month to Month quick measure is almost what I want, but not quite.  I have modified it below to return the $ value instead of the percentage.  The problem is the Previous month is always -1 and I either need it to be fixed on the base month. or increment, like -1 then -2 then -3 etc.

For example if I choose all of 2021 I want the previous month calculation to always be January while the current month cycles through all of the months.

Also the range will be user selected so the first month could be any month and the duration could be any length.

Any ideas?

Savings =

IF(

    ISFILTERED('Telecom wireline all years'[Accounting Period]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_MONTH =

        CALCULATE(

            SUM('Telecom wireline all years'[Distribution Amount]),

            DATEADD('Telecom wireline all years'[Accounting Period].[Date], -1, MONTH

            )

        )

    RETURN

            SUM('Telecom wireline all years'[Distribution Amount]) - __PREV_MONTH

)

[v-stephen-msft]

Hi @daviddavidson ,

Here's the solution in Power Query.

1.Add a custom column to return the value of the current row minus the value of the first row.

2.After the type of the custom column is changed as number type, add another custom column to return base amount.

Hope it helps.

You can download my attachment for more details.

Best Regards,

Stephen Tao

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

[ImkeF]

Hi @daviddavidson ,
if totals don't matter, you can use a measure like this:

VAR __FirstDate =
    CALCULATE (
        MIN ( 'Date'[Date] ),
        ALLSELECTED ( 'Date'[Date] ),
        REMOVEFILTERS ( 'Date' )
    )
VAR __FirstAmount =
    CALCULATE ( SUM ( 'Table'[Amount] ), 'Date'[Date] = __FirstDate )
VAR __Result =
    SUM ( 'Table'[Amount] ) - __FirstAmount
RETURN
    __Result

but if you want the total to always display the difference of the latest month, then this would work:

VarLastToFirstValue = 
VAR __FirstDate =
    CALCULATE (
        MIN ( 'Date'[Date] ),
        ALLSELECTED ( 'Date'[Date] ),
        REMOVEFILTERS ( 'Date' )
    )
VAR __FirstAmount =
    CALCULATE ( SUM ( 'Table'[Amount] ), 'Date'[Date] = __FirstDate )
VAR __LastAmount =
    CALCULATE ( SUM ( 'Table'[Amount] ), 'Date'[Date] = MAX ( 'Table'[Date] ))
VAR __Result =
    __LastAmount - __FirstAmount
RETURN
    __Result

Please also check the file enclosed.