MOM, QoQ, YoY calculation

[Anonymous]

Hi,

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

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:

Please help me at the earliest,

REgards,

varun

[v-lili6-msft]

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

[Anonymous]

Hi All,

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

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

[Ashish_Mathur]

Hi,

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

[Anonymous]

Hello Ashish,

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

REgards,

Varun

[Ashish_Mathur]

Hi,

Paste the data in the body of the message.

[Anonymous]

Hi,

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

 

[Ashish_Mathur]

Hi,

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

[Anonymous]

https://drive.google.com/file/d/1XCvSoDkbGI8JwcMbEsGKjbSmAmLnuwEW/view?usp=sharing

[Anonymous]

Hi Avisha,

Please let  me know if you could view the file.

Thanks,

Varun

[Anonymous]

Hello Ashish,

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

REgards,

Varun

[Anonymous]

Hello All,

Please help me with a solution.

REgards,

Varun

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

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

[Ashish_Mathur]

Hi,

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

[Anonymous]

I understand.

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

Regards,

Varun

[v-lili6-msft · 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

[Anonymous]

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

[Ashish_Mathur]

You are welcome.

[Anonymous]

I understand.

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