Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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,
In date slicer, you have relative date option.
Regards,
Lydia
@dalemoy,
Using your sample data, I can successfully create quick measures. What error message do you get?
Regards,
Lydia
Hi
Can you share the DAX used to get YoY, QoQ, MoM.
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?
@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
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?
@dalemoy,
In date slicer, you have relative date option.
Regards,
Lydia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |