Forum Discussion

tomba_96's avatar
tomba_96
New Member
3 years ago
Solved

%monthly change

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

Need Help
  • parry2k's avatar
    parry2k
    3 years ago

    tomba_96 what measure are you using for the previous month?

     

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

5 Replies

  • tomba_96's avatar
    tomba_96
    New Member
    3 years ago

    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's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

    tomba_96 what measure are you using for the previous month?

     

    PREVIOUS_MONTH_VALUE = CALCULATE([TOTAL_AMOUNT], DATEADD ( 'Calendar'[Date], -1,MONTH))
    • tomba_96's avatar
      tomba_96
      New Member
      3 years ago

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

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

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