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
- GilbertQHi there I would suggest changing the >= to <= ?
Hi,
I have a list of 5 State Financial years ranging from 2013 to 2018. I am able to calculate difference between two consecutive years(2017,2018) and difference between current year 2018 with any previous year but not able to calculate difference for previous years like (2015,2017)
The DAX functions I used here are:YTD= Total YTD([Value], (Date), “2018/6/30”)
PYTD = Calculate([YTD], Datesbetween(Date), Date(2013,7,1), Date(2017,6,30)))
Can someone assist me here? Thanks!- GilbertQHi there, you could do it with the following
First ensure that you have a date table, and have it marked as a Date table.
PYTD = Calculate([YTD], PREVIOUSYEAR('Date'[Date]))