Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Power BI All Stars:
I have 10 years worth of sales data - seeking help with calculating the percentage change for current month (November 2017) over same month last year (November 2016). Also, is there a way to calculate YTD %'age change - e.g., Jan - Nov 2017 versus Jan - Nov 2016?
Thank you for helping a Power BI novice! ~DentonBlake
Solved! Go to Solution.
Dentonblake
1. Exclude from the table the columns [total_recipets], [TABC_SALES], [Quarter], [Day]
Do not forget that the KPI you created is monthly, any column you use can lead to inconsistencies in the data.
And the most appropriate filter is YEAR.
Include a YEAR SEGMENTATION.
It will get better.
if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.
Best Regards,
Rfranca
Hi,
You may use the CALCULATE() and SAMEPRIODLASTYEAR() functions. To get more specific help, share some data.
Link to snapshot of data -> https://retailstreet.box.com/s/w96ae6bj1bgkkwhx2t4r8w20gbv6qm17
Hi @dentonblake,
I do not understand your data. Why are the Year, Quarter. Month and Day columns being repeated in your dataset?
Hi, dentonblake
try
SLI_VOLUME = SUM(ft_SELLIN[QUANTIDADE]))
xMonthActual_SLI = TOTALMTD([SLI_VOLUME];dm_CALENDAR[Date])
xMonthPreviousToDate_SLI = TOTALMTD([SLI_VOLUME];DATESMTD(DATEADD(dm_CALENDAR[Date];-1;YEAR)))
xPercent_MTDActualvsLastYear = IFERROR( DIVIDE([xMonthActual_SLI];[xMonthPreviousToDate_SLI];0);0)
YES! you Do I need to create a Calendar table.
The relationship between the two tables is dm_CALENDAR[Date] with TABC[obligation_end_date_yyyymmdd] or another field that relates the sales date.
Whenever you use KPIs with respect to time, you should create a CALENDAR table. With it you can create other time-related KPIs like YTD, YTG, YoY MTD and others.
Thank you, again, Rfranca ... very helpful!
Can the calendar table have one column for the date or does it require multiple columns for month, year, quarter, etc...?
~db
A CALENDAR table has several columns all of these columns help in calculations of various periods like month, year, quarter, week, day among others. and also has columns for the presentation of these periods in dashboards, something like JANUARY, JAN, Q1, MONDAY, etc.
So a CALENDAR tabela should be very complete according to your needs. Another important point is the start and end of the date range, that is the first date and the last date being that this interval should be compatible with your sales data.
On the internet you have some ready examples of table CALENDAR
Still doing something wrong ... I wonder if it's the relationship with the Date (Calendar) Table?
Not pulling data for xMonthActual or xMonthPreviousToDate.
Thank you, ~db
Let's see what's wrong ...
1. There is a CALENDAR table (dm_CALENDARY)
2. There is a field in this CALENDAR table called [Date] (dm_CALENDAR [Date])
3. This field is this date format;
4. There is a data table that you called TBC;
5. There is a date (sales) field in this TBC table which must be the field TABC [obligation_end_date_yyyymmdd]
6. This field is this date format;
7. There is a relationship between the dm_CALENDAR [Date] table fields with TABC [obligation_end_date_yyyymmdd]
8. All field dates in the TABC table [obligation_end_date_yyyymmdd] exist in the table field dm_CALENDAR [Date]
All of the above should be ok.
For the image sent the TABC [obligation_end_date_yyyymmdd] field is in the wrong format, it is this date-time and should be just date.
try again now
Changing the date format worked (thank you), however, the %'age change measure is causing an error - screenshot below:
Also, is there a way to consolidate data on one row for each Location_Name?
Thank you! ~db
%'age change measure results in 0.00% - any suggestions? Thank you, ~db
All measures are working now - last question: how do I filter so only the "comparison row" is showing (top row in the image below). Thank you, ~db
Dentonblake
1. Exclude from the table the columns [total_recipets], [TABC_SALES], [Quarter], [Day]
Do not forget that the KPI you created is monthly, any column you use can lead to inconsistencies in the data.
And the most appropriate filter is YEAR.
Include a YEAR SEGMENTATION.
It will get better.
if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.
Best Regards,
Rfranca
Rfranca-
Thank you so much - I think I'm getting closer to the desired result.
Is there a way to consolidate the data on one row - see below? Also, since I'm new to Power BI, I don't think if I fully understand "YEAR SEGMENTATION" ... is this a new measure I should add?
Best, ~db
Got it! Only took an entire weekend - HA!
Thank you for all of the assistance!
~db
Hi @dentonblake,
Based on my understanding, please create the measure using the formula and check if it works fine.
xPercent_MTDActualvsLastYear = IFERROR ( DIVIDE ( [xMonthActual_SLI], [xMonthPreviousToDate_SLI] ), 0 )
Please feel free to ask if you have any other issue.
Best Regards,
Angelia
Angelia, thank you for your response. When I activate or check the measure field for this calculation it processes for about 10 - 15 minutes and then generates an error. ~db
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |