Forum Discussion
Values for Corresponding previous period and current period when using a date range
Hi Senarath
No problem, Variables are something that is fairly new.
Here is a sample below, please note that I have created a Date Table to use the DateAdd Function. You can find my blog post here on how to create and map a Date table: https://www.fourmoo.com/2016/09/13/power-bi-how-to-easily-create-dynamic-date-tabledimension-with-fiscal-attributes-using-power-query/
Date Range Back Same Period = VAR MinDate = MIN('Date'[Date]) VAR MaxDate = MAX('Date'[Date]) RETURN CALCULATE([My Measure],FILTER(ALL('Date'[Date]),DATEADD('Date'[Date],+1,YEAR) >= MinDate && DATEADD('Date'[Date],+1,YEAR) <= MaxDate) )
Super UserHi Senarath
No problem, Variables are something that is fairly new.
Here is a sample below, please note that I have created a Date Table to use the DateAdd Function. You can find my blog post here on how to create and map a Date table: https://www.fourmoo.com/2016/09/13/power-bi-how-to-easily-create-dynamic-date-tabledimension-with-fiscal-attributes-using-power-query/
Date Range Back Same Period =
VAR MinDate = MIN('Date'[Date])
VAR MaxDate = MAX('Date'[Date])
RETURN
CALCULATE([My Measure],FILTER(ALL('Date'[Date]),DATEADD('Date'[Date],+1,YEAR) >= MinDate
&& DATEADD('Date'[Date],+1,YEAR) <= MaxDate)
)
Hi guavaq,
One more thing that I missed out to get clarified. In a case where I want to use two dates viz Check In Date and Check Out Date. And I want to filter "After" Check In Date and "Before" Check Out Date as the date range,
How should the below variable should be changed ?
Date Range Back Same Period =
VAR MinDate = MIN('Date'[Date])
VAR MaxDate = MAX('Date'[Date])
RETURN
CALCULATE([My Measure],FILTER(ALL('Date'[Date]),DATEADD('Date'[Date],+1,YEAR) >= MinDate
&& DATEADD('Date'[Date],+1,YEAR) <= MaxDate)
)
Thanks
Senarath
- GilbertQHi there I would suggest changing the >= to <= ?
Hi Guavaq,
How about this one as I have modified?
Cost LY =
VAR MinDate = MIN(RoomNights[Check In Date])
VAR MaxDate = MAX(RoomNights[Check Out Date])
RETURN
CALCULATE([Cost],FILTER(ALL('RoomNights'[Check In Date]),DATEADD(RoomNights[Check In Date],+1,YEAR) >= MinDate
&& DATEADD(RoomNights[Check Out Date],+1,YEAR) <= MaxDate)
)Please correct it for me if wrong.
Thanks
- GilbertQThat does look correct. Did you try it and see if it got you the correct results?