support with formulas in power bi

[DMinero]

Hello!

I am currently working a base in power bi where I have a monthly balance column for each client, so what I want to know how much each client of the base has amortized every month.

My problem is that the client is repeat in all the months of the year in the same column, if the client repeats in a column per month could do in the simple way subtract but I do not know if any of you could provide me with a solution.

thanks in advance.

[MFelix · Accepted solution]

Hi @DMinero,

Not sure how you information is setup but you can do a measure like this:

Amortized =
TOTALMTD ( SUM ( Table[Value] ), Calendar[Date] )
    - TOTALMTD ( SUM ( Table[Value] ), DATEADD ( Calendar[Date], -1, MONTH ) )

If you add this to your table with customer name and month you should have what you need change the columns to the ones on your model.

Regards,

MFelix

[DMinero]

thnks to anwers, just a quick question calendar is a table ?

[MFelix]

Hi @DMinero,

Calendar is a table that is.linked to the dates in your data model and used in calculations and.visuals.

[DMinero]

HI MFelix  Sorry for bothering I do the calculation as indicated, but it only subtracted 1 dollar instead of subtracting the amount of the previous month. 

Could you tell me if I can do two simple columns:

1. Column MTD, which I already have

2. Column previous month

In this way, I could proceed and just make a simple subtraction.

Thnks in advance.

[MFelix]

Hi @DMinero,

If you do the split of my formula you have two measures:

Total MTD =TOTALMTD ( SUM ( Table[Value] ), Calendar[Date] )



Total PY MTD = TOTALMTD ( SUM ( Table[Value] ), DATEADD ( Calendar[Date], -1, MONTH ) )

Regards,

MFelix