cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Community Support
Community Support

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

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 it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

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

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 it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

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

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors