Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JP_M_Shep
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.

 

GroupDateScorePrevMonthDate PrevMonthScore 
A4/1/2016100  
B4/1/2016105  
C4/1/2016110  
D4/1/2016120  
A5/1/2016120  
B5/1/2016105  
C5/1/2016110  
D5/1/2016100  
A6/1/2016105  
B6/1/2016120  
C6/1/2016100  
D6/1/2016110  
A7/1/2016100  
B7/1/2016105  
C7/1/2016110  
D7/1/2016120  
A8/1/2016120  
B8/1/2016105  
C8/1/2016110  
D8/1/2016100  
A9/1/2016105  
B9/1/2016120  
C9/1/2016100  
D9/1/2016110  


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" ))

 

Date Dim.JPG

 

There is a 1 to many relationship 'Date'[Date] ==> 'Sheet2'[Date]

 

What I would expect to see is as follows:

 

GroupDateScorePrevMonthDate PrevMonthScore 
A4/1/2016100  
B4/1/2016105  
C4/1/2016110  
D4/1/2016120  
A5/1/20161204/1/2016100
B5/1/20161054/1/2016105
C5/1/20161104/1/2016110
D5/1/20161004/1/2016120
A6/1/20161055/1/2016120
B6/1/20161205/1/2016105
C6/1/20161005/1/2016110
D6/1/20161105/1/2016100
A7/1/20161006/1/2016105
B7/1/20161056/1/2016120
C7/1/20161106/1/2016100
D7/1/20161206/1/2016110
A8/1/20161207/1/2016100
B8/1/20161057/1/2016105
C8/1/20161107/1/2016110
D8/1/20161007/1/2016120
A9/1/20161058/1/2016120
B9/1/20161208/1/2016105
C9/1/20161008/1/2016110
D9/1/20161108/1/2016100

 

 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
v-shex-msft
Community Support
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])

1.PNG

 

 

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.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
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])

1.PNG

 

 

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.