Calculating Variance: Year on Year, month on month etc

[dalemoy]

Hi all,

Another noobie question here so apologies if i'm wasting anybodies time.

I have 2 types of dataset's I need to apply this to, an SQL db and our salesforce data.

for each I need to display the variance from the previous period (day, week, month, quarter and year) and the percentage change.

In a perfect world, i'd have KPI or similar visual showing this change for each.

I have already tried a number of solutions involved in using measures, quick measures, and columns, all to no success.

Below is an example of my data. this example is reprensetative of both datasets. 

INVOICE_ID	NET TOTAL	DATE_ISSUED
SO00001	£     508.00	28/11/2015
SO00001	£     442.00	19/02/2017
SO00003	£ 1,182.00	07/12/2014
SO00003	£ 2,318.00	06/06/2016
SO00006	£ 1,885.00	27/02/2015
SO00006	£ 1,562.00	31/10/2017
SO00007	£ 1,549.00	17/06/2017
SO00007	£ 2,489.00	05/04/2016
SO00009	£ 1,117.00	28/08/2014

[v-yuezhe-msft · Accepted solution]

@dalemoy,

Instead of using Quick measures, please follow the guide in the similar thread and blog below to calculate YOY and MOM, then you are able to put the measures into Card visuals. And you can use date slicer to filter the measures.

https://community.powerbi.com/t5/Desktop/Year-over-Year-calculation/td-p/75403
https://powerbi.tips/2016/07/measures-month-to-month-percent-change/


Regards,
Lydia

[v-yuezhe-msft]

@dalemoy,

Using your sample data, I can successfully create quick measures. What error message do you get?



Regards,
Lydia

[Anonymous]

Hi

Can you share the DAX used to get YoY, QoQ, MoM.

[dalemoy]

Hi Lydia,

Thank you so much for your quick reply.

This is embarrassing, but it now seems to work with the correct numbers - maybe my brain stopped working?

my follow up questions are:

- I want to view this information as either a KPI (up/down) or a Card for the current month/quarter/year. however, when I remove the date hierarchy, the visuals (even matrix) stop working.

       - ideally, i would set a relative date filter for this month, this year etc - but this doesnt work?

-silly question, but if i wanted to compare this month, to the month last year would I change the period in the quick measure from 1 to 13 (jan 2017 against Jan 2018)? or would I choose a year-over-year quick measure and find some way to filter it by month only?

[v-yuezhe-msft · Accepted solution]

@dalemoy,

Instead of using Quick measures, please follow the guide in the similar thread and blog below to calculate YOY and MOM, then you are able to put the measures into Card visuals. And you can use date slicer to filter the measures.

https://community.powerbi.com/t5/Desktop/Year-over-Year-calculation/td-p/75403
https://powerbi.tips/2016/07/measures-month-to-month-percent-change/


Regards,
Lydia

[dalemoy]

Hi Lydia,

Thanks for replying. 

If I was to use a slicer, I wouldnt have a relative date filter correct? e.g. this calendar week, this calendar month etc. it would be something i'd have to manually change on the slicer every time? 

[v-yuezhe-msft · Accepted solution]

@dalemoy,

In date slicer, you have relative date option.



Regards,
Lydia