Forum Discussion
Measure Get Previous Period
My calendar table has non-traditional periods. I have a column in the table called Period Name which is a text field. This field is the filter on my page. An example would be 2020P02.
When the page is filtered for 2020P02, I want my measure to return 2020P01. In order to accomplish this, I have a column called Index which is a cumulative count of all the Period Names. So 2020P02 would have an Index value of 15 and 2020P01 would have an Index value of 14.
I'm trying to hvae my measure ignore the Period Name filter on the page and return the values related to the previous period or index value 14. This is my attempt and I'm not sure why it's not working:
Total Sales Previous Period =
VAR prevperiod =
CALCULATE (
MAX ( 'Calendar'[Index] ),
FILTER (
'Calendar',
'Calendar'[periodName]
= SELECTEDVALUE ( 'Calendar'[periodName] )
)
) - 1
RETURN
CALCULATE (
[Total Sales Dashboard],
'Calendar'[Index] = prevperiod,
ALL ( 'Calendar' )
)2 Replies
Anonymous
Your Measure should work, try it this way. Make sure you have one to many relationships between Calendar and the Fact Table on Index column.Total Sales Previous Period = VAR prevperiod = LASTNONBLANK(Calendar[INDEX],SELECTEDVALUE(Calendar[PERIOD NAME])) - 1 RETURN CALCULATE ( [Total Sales Dashboard], 'Calendar'[Index] = prevperiod, ALL(Calendar) )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
Anonymous
Has the problem been solved? If so, you may accept the appropriate reply as the solution or post your own solution to help close this topic. If not, can you please share some sample data in table format or a sample PBIX file (remove sensitive information) so that we can help further.Thanks.
Best Regards,
Community Support Team _ Jing Zhang
