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
Anonymous
Not applicable

MOM, QoQ, YoY calculation

Hi,

 

This is regards to the MoM, QoQ & YoY calcialtion of Variance,Financial Year starts from April. attached is the data Format.

 

image.png

 

Just similar to above i ve a data since FY14 till date.

Note: 

1.Fy starts from April

2. Clients are duplicated in each month along with revenue.

Need to calcualte variance MoM, QoQ and YoY in the format below:

image.png

Please help me at the earliest,

 

REgards,

varun

1 ACCEPTED SOLUTION

hi, @Anonymous 

Just change the table visual to matrix visual and drag Financial Year field into Columns value.

See this pbix file as below. 

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

19 REPLIES 19
v-lili6-msft
Community Support
Community Support

 

hi, @Anonymous 

You need to add a Financial calendar table and then create the relationship with data "Month" column.

Then use DATEADD or PREVIOUSMONTH Function to calculate previous month data as below:

 

Measure = CALCULATE(SUM('Table'[Sales]), DATEADD('FY calendar'[Date], -1, MONTH))

or

 

Measure = CALCULATE(SUM('Table'[Sales]), PREVIOUSMONTH('FY calendar'[Date]))

Then drag date field from Financial calendar table into visual

If not your case, please share some simple and complete data with your expected output.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi All,

 

After some workout on the data there are some findings as mentioned below:

 

image.pngimage.png

 

1. As mentioned, my fianncial Yaer starts from April to Mar and 've a data from FY14.

2.  Used All select Month name to filter but could not get

 

Hi,

Share some data in a format that can be pasted in MS Excel.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hello Ashish,

 

Sorry, could not attach the file in this portal. Please find the view of the data.

 

REgards,

Varunimage.png

Hi,

Paste the data in the body of the message.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

 

Hi,

Tried to copy the data but could not paste since i was getting below error.

 image.png

Hi,

Upload the file to Google Drive and share the download link here.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable
Anonymous
Not applicable

Hi Avisha,

 

Please let  me know if you could view the file.

 

Thanks,

Varun

Anonymous
Not applicable

Hello Ashish,

 

Ideally, my target is to find the difference as shown below.

 

image.png

 

REgards,

Varun

Anonymous
Not applicable

Hello All,

 

Please help me with a solution.

 

REgards,

Varun

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thanks Ashish for your response,

by any chance do we have the possibility to solve this without using  Filters for Financial Year?

 

I mean, possible to find the total of SamePeriod previous Year sales at row level for each Month. Like Sum of all April related sales, May Sales etc...

 

Regards,

Varun

Hi,

I do not understand your question but for computing same period last year, you will have to select a specific year.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

I understand.

 

I mean to say similar to the below format ist possible ? where can view the variance for each FY at column level? 

 

image.png

 

Regards,

Varun

hi, @Anonymous 

Just change the table visual to matrix visual and drag Financial Year field into Columns value.

See this pbix file as below. 

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

I'd issue with my file with regards to the Relationships hence i could not get the solution.

 

Thanx Ashish and all for your support

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

I understand.

 

Imean to say in the below Format. Where we can view the Current and Previous numbers for all the months 

image.png

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.