Values for Corresponding previous period and current period when using a date range

[Senarath]

Hi Experts,

I'm using a date slicer with the display option "Between" so that I can select a date range. I need two columns in a single table for current period and corresponding previous period (e.g. May-17 Vs May-16). So when I select a date range which could be a week, a month, a Qtr or an year, the two columns should show values for the selected periods both current and corresponding previous.

I tried with functions such as DATEADD ( counts a full year), PREVIOUSYEAR (counts a full year), SAMEPERIODLASTYEAR (not working for date slicer with a renage), PARALLELPERIOD (again count full year or month ). But still my requirement is not met.

Any help please....

Thanks

Senarath

[GilbertQ]

Hi @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?

[Senarath]

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 · Accepted solution]

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-fi...

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)
)

[Anonymous]

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!

[GilbertQ]

Hi 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]))

[Anonymous]

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])) 

[Senarath]

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

[GilbertQ]

Hi there I would suggest changing the >= to <= ?

[Senarath]

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

[GilbertQ]

That does look correct. Did you try it and see if it got you the correct results?

[Senarath]

Hi guavaq,

Nope my friend. I got this error now

[GilbertQ]

Hi there the error appears to be due to the fact that there is no Date table in your data, which has a date for each and every day, even if you do not have data.

I did put this into my first post where I detailed how to get it implemented on your data.