Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
smpa01
Super User
Super User

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

Matrix VisualizationMatrix Visualization

I want to create a measure which will calculate the MoM%Change based on the %CT Count as demonstrated in the "Desired Tab" as following. Is it possible to create such a measure in DAX?

 

DesiredDesired

Thank you in advance

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

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())

Capture.PNG  

 

Regards,

Jimmy Tao

View solution in original post

1 REPLY 1
v-yuta-msft
Community Support
Community Support

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())

Capture.PNG  

 

Regards,

Jimmy Tao

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.