Forum Discussion

smpa01's avatar
smpa01
Community Champion
7 years ago
Solved

MoM% Change based on %CT Count

Hi,   I have a source table as attached (Tab- RAW_Data). https://drive.google.com/open?id=1-D-PzY3u2M5hWhMTp7Oeo1URDpJA_st1   I have created a Matrix Table which gives me the %CT Count over ...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    Hi smpa01,

     

    To achieve your requirement, you should create an index firstly. Click Query Editor-> Add Column->Index Column. After close&applied, create a calculate column using DAX below:

    Change = 
VAR previous_month = CALCULATE(MAX(Desired[Month]), FILTER(Desired, Desired[Index] = EARLIER(Desired[Index]) - 1))
VAR current_month = Desired[Month]
VAR previous_month_Count = CALCULATE(MAX(Desired[%CT Count of REC to PMT Ageing]), FILTER(Desired, Desired[Index] = EARLIER(Desired[Index]) - 1))
VAR current_month_count = Desired[%CT Count of REC to PMT Ageing]
RETURN
IF(current_month <> "July", (current_month_count - previous_month_Count) / previous_month_Count, BLANK())

      

     

    Regards,

    Jimmy Tao

v-yuta-msft's avatar
v-yuta-msft
Community Support
7 years ago

Hi smpa01,

 

To achieve your requirement, you should create an index firstly. Click Query Editor-> Add Column->Index Column. After close&applied, create a calculate column using DAX below:

Change = 
VAR previous_month = CALCULATE(MAX(Desired[Month]), FILTER(Desired, Desired[Index] = EARLIER(Desired[Index]) - 1))
VAR current_month = Desired[Month]
VAR previous_month_Count = CALCULATE(MAX(Desired[%CT Count of REC to PMT Ageing]), FILTER(Desired, Desired[Index] = EARLIER(Desired[Index]) - 1))
VAR current_month_count = Desired[%CT Count of REC to PMT Ageing]
RETURN
IF(current_month <> "July", (current_month_count - previous_month_Count) / previous_month_Count, BLANK())

  

 

Regards,

Jimmy Tao