Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I am fairly new to Power BI and have had the request to display the variance between months for each ID for each month.
I cannot calculate Previous Month date - so if May i would want to see date as at April. Similarly, I cannot calculate Previous Month score, so if May i would want to see score as at April. AS per below, I have provided the examples.
Until I can see the previous month's information, I cannot work out the variance (which i can do, that isn't the problem).
I have 2 tables
Sheet2 - 1 imported from Excel - with 24 rows:
Group (A-D),
Date (Date Value) - 1st April to 1st Sept,
Score (Whole Number Value),
PrevMonthDate (Date Value and calculated field - PreviousMonthDate = LOOKUPVALUE('Sheet2'[Date],'Sheet2'[Date],PREVIOUSMONTH('Date'[Date].[Date]))) and
PrevMonthScore (Whole Number Value and calculated field - PreviousMonthDate = LOOKUPVALUE('Sheet2'[Date],'Sheet2'[Date],PREVIOUSMONTH('Date'[Date].[Date])))
This is how the table looks - and as you can see PrevMonthDate and PrevMonthScore just appear blank, even though they are both formula driven.
Group | Date | Score | PrevMonthDate | PrevMonthScore |
A | 4/1/2016 | 100 | ||
B | 4/1/2016 | 105 | ||
C | 4/1/2016 | 110 | ||
D | 4/1/2016 | 120 | ||
A | 5/1/2016 | 120 | ||
B | 5/1/2016 | 105 | ||
C | 5/1/2016 | 110 | ||
D | 5/1/2016 | 100 | ||
A | 6/1/2016 | 105 | ||
B | 6/1/2016 | 120 | ||
C | 6/1/2016 | 100 | ||
D | 6/1/2016 | 110 | ||
A | 7/1/2016 | 100 | ||
B | 7/1/2016 | 105 | ||
C | 7/1/2016 | 110 | ||
D | 7/1/2016 | 120 | ||
A | 8/1/2016 | 120 | ||
B | 8/1/2016 | 105 | ||
C | 8/1/2016 | 110 | ||
D | 8/1/2016 | 100 | ||
A | 9/1/2016 | 105 | ||
B | 9/1/2016 | 120 | ||
C | 9/1/2016 | 100 | ||
D | 9/1/2016 | 110 |
Date -
Date = ADDCOLUMNS ( CALENDAR(MINX( 'Sheet2', [Date]) , MAXX ('Sheet2', [Date]) ), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "MonthNo", FORMAT ( [Date], "MM" ), "YearMonthNo", FORMAT ( [Date], "YYYY/MM" ), "YearMonth", FORMAT ( [Date], "YYYY/mmm" ), "MonthShort", FORMAT ( [Date], "mmm" ), "MonthLong", FORMAT ( [Date], "mmmm" ), "WeekNo", WEEKDAY ( [Date] ), "WeekDay", FORMAT ( [Date], "dddd" ), "WeekDayShort", FORMAT ( [Date], "dddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))
There is a 1 to many relationship 'Date'[Date] ==> 'Sheet2'[Date]
What I would expect to see is as follows:
Group | Date | Score | PrevMonthDate | PrevMonthScore |
A | 4/1/2016 | 100 | ||
B | 4/1/2016 | 105 | ||
C | 4/1/2016 | 110 | ||
D | 4/1/2016 | 120 | ||
A | 5/1/2016 | 120 | 4/1/2016 | 100 |
B | 5/1/2016 | 105 | 4/1/2016 | 105 |
C | 5/1/2016 | 110 | 4/1/2016 | 110 |
D | 5/1/2016 | 100 | 4/1/2016 | 120 |
A | 6/1/2016 | 105 | 5/1/2016 | 120 |
B | 6/1/2016 | 120 | 5/1/2016 | 105 |
C | 6/1/2016 | 100 | 5/1/2016 | 110 |
D | 6/1/2016 | 110 | 5/1/2016 | 100 |
A | 7/1/2016 | 100 | 6/1/2016 | 105 |
B | 7/1/2016 | 105 | 6/1/2016 | 120 |
C | 7/1/2016 | 110 | 6/1/2016 | 100 |
D | 7/1/2016 | 120 | 6/1/2016 | 110 |
A | 8/1/2016 | 120 | 7/1/2016 | 100 |
B | 8/1/2016 | 105 | 7/1/2016 | 105 |
C | 8/1/2016 | 110 | 7/1/2016 | 110 |
D | 8/1/2016 | 100 | 7/1/2016 | 120 |
A | 9/1/2016 | 105 | 8/1/2016 | 120 |
B | 9/1/2016 | 120 | 8/1/2016 | 105 |
C | 9/1/2016 | 100 | 8/1/2016 | 110 |
D | 9/1/2016 | 110 | 8/1/2016 | 100 |
I had done this elsewhere with RANKX and it was too complicated as had to do a set of columns per Group so not efficient as the dataset I want to ultimately use this on runs into the millions.
Please could anybody help me sort this out?
Solved! Go to Solution.
Hi @JP_M_Shep,
You can try to use below formula to get the previous value:
Previous Date = DATEADD(Sheet3[Date],-1,MONTH)
Previous Score = CALCULATE(MAX(Sheet3[Score]),FILTER(ALL('Sheet3'),[Group]=EARLIER(Sheet3[Group])&&[Date]=EARLIER(Sheet3[Previous Date])))
Previous Score2 = LOOKUPVALUE(Sheet3[Score],Sheet3[Date],[Previous Date],Sheet3[Group],[Group])
BTW, some date function(PREVIOUSMONTH, SAMEPERIODLASTYEAR, DATESMTD...) seems works when you use calendar date to filter. If you try to use these function on single table, it not works and give you blank result.
Regards,
Xiaoxin Sheng
Hi @JP_M_Shep,
You can try to use below formula to get the previous value:
Previous Date = DATEADD(Sheet3[Date],-1,MONTH)
Previous Score = CALCULATE(MAX(Sheet3[Score]),FILTER(ALL('Sheet3'),[Group]=EARLIER(Sheet3[Group])&&[Date]=EARLIER(Sheet3[Previous Date])))
Previous Score2 = LOOKUPVALUE(Sheet3[Score],Sheet3[Date],[Previous Date],Sheet3[Group],[Group])
BTW, some date function(PREVIOUSMONTH, SAMEPERIODLASTYEAR, DATESMTD...) seems works when you use calendar date to filter. If you try to use these function on single table, it not works and give you blank result.
Regards,
Xiaoxin Sheng
Thank you for your help! Perfect! More reading for me to do!!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |