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) )
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
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
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]))Thanks for the reply. I did add a Date table and included the Date column into the below DAX functions.
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)))Are the above DAX functions incorrect?
Where should I put in the Previous year date values into the DAX function you had mentioned
PYTD = Calculate([YTD], PREVIOUSYEAR('Date'[Date]))