cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Diplaying the previous Months (for each month) where multiple IDs and Dates

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?

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
2 REPLIES 2
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

Thank you for your help! Perfect! More reading for me to do!!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.