Forum Discussion
AdministratorCompare vs same previous period.
Hello.
I'm trying to see how to compare two consecutive periods taking into account that they last the same. That is, I have a table with only 2 columns (Date, Sale) that covers all of 2022. If I filter for example, from July 15 to August 3 (20 days) what I need is to give me the comparison vs the previous 20 days, that is, the sale from June 25 to July 14 (20 days too).
I know that it can be done with two custom periods, which involves manually defining both periods, but what I am looking for is that you can define the previous period automatically just by defining the current period.
Thanks a lot.
Syndicate_Admin , Assuming you have selected one range
you can try measures like
//previous period
Same Date range last period =
var _diff = datediff(MIN('Date'[date]),max('Date'[date]),DAY)
var _p_st_date = MINX('Date',DATEADD('Date'[date],-1*_diff,Day))-1
var _p_end_date = MAXX('Date',DATEADD('Date'[date],-1*_diff,Day))-1
Return
CALCULATE(sum(Sales[Sales Amount]),all('Date'[date]),'Date'[date]>=_p_st_date && 'Date'[date]<= _p_end_date
)
Same Date range last period =
var _diff = datediff(MIN('Date'[date]),max('Date'[date]),DAY)
var _p_st_date = MINX('Date',DATEADD('Date'[date],-1*_diff,Day))-1
var _p_end_date = MAXX('Date',DATEADD('Date'[date],-1*_diff,Day))-1
Return
CALCULATE(sum(Sales[Sales Amount]),dateadd('Date'[date],-1*_diff,Day)If you want two compare two date ranges
How to use two Date/Period slicers
1 Reply
- amitchandak
Super User
Syndicate_Admin , Assuming you have selected one range
you can try measures like
//previous period
Same Date range last period =
var _diff = datediff(MIN('Date'[date]),max('Date'[date]),DAY)
var _p_st_date = MINX('Date',DATEADD('Date'[date],-1*_diff,Day))-1
var _p_end_date = MAXX('Date',DATEADD('Date'[date],-1*_diff,Day))-1
Return
CALCULATE(sum(Sales[Sales Amount]),all('Date'[date]),'Date'[date]>=_p_st_date && 'Date'[date]<= _p_end_date
)
Same Date range last period =
var _diff = datediff(MIN('Date'[date]),max('Date'[date]),DAY)
var _p_st_date = MINX('Date',DATEADD('Date'[date],-1*_diff,Day))-1
var _p_end_date = MAXX('Date',DATEADD('Date'[date],-1*_diff,Day))-1
Return
CALCULATE(sum(Sales[Sales Amount]),dateadd('Date'[date],-1*_diff,Day)If you want two compare two date ranges
How to use two Date/Period slicers