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
What you could do is to create a measure using Variables which would get your min and max dates.
And then based on that you could go back one year based on the min and max dates?
Hi guavaq,
Sorry I'm not clear on what you recommend. Further, I do not know that we can use variables in Power BI. Please explain with an example if possible.
Thks
Senarath
- GilbertQ
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) )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
