%monthly change

[tomba_96]

Dear all,

I have a request for you, would you help me in creating a monthly percentage change?

For example I have 3 companies, each company have some total value for each month (period) and account. So first I made a new column as "End of the Month" - date type. The table you can see below

First, I created a measure for Total Amount

TOTAL_AMOUNT = SUM (TEST[TOTAL VALUE])

This KPI works with the "Company" filter which means that if I selected "Company" A and "Period" 1 in the filters, the total sum is 250. 

Then I created a second measure for Previous Month Value:

PREVIOUS_MONTH_VALUE = CALCULATE([TOTAL_AMOUNT], PARALLELPERIOD(TEST[END OF THE MONTH], -1,MONTH))

And this is probably where the problem arises. Because if I filter only period 2, I don't see any values in the "Previous Value" column in the table. However, if I select all companies and all periods, I get the total value minus the total value for period 3 in the total.

I would need the metric to work by showing me the monthly difference between within each company and then the % difference displayed using the KPI visual, which I assume is a fairly simple metric in the form of

%MONTHLY_CHANGE = DIVIDE([TOTAL_AMOUNT] - [PREVIOUS_MONTH_VALUE], [PREVIOUS_MONTH_VALUE])

My idea is something like this:

Please advise me how to achieve this. I have tried different types of filters but nothing has shown me the results I would like.

Thank you very much in advance

[parry2k]

@tomba_96 sounds good. Mark the reply as a solution so that others can get help from it. Cheer!

[parry2k · Accepted solution]

@tomba_96 what measure are you using for the previous month?

PREVIOUS_MONTH_VALUE = CALCULATE([TOTAL_AMOUNT], DATEADD ( 'Calendar'[Date], -1,MONTH))

[tomba_96]

Thank you, I've already figured out that I have to use DATEADD. Thank you again

[tomba_96]

Thank you for your reply.

I have created a list of dates using List.Dates function in M language, which I then converted as a table and added columns with start of month, end of month,....

But still, after selecting the filter and the period, the total value in the last month for that company is not displayed

What am I still doing wrong?

Thank you

[parry2k]

@tomba_96 

As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Time Intelligence Playlist