Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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) )
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?
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
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) )
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!
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]))
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
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 guavaq,
Nope my friend. I got this error now
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |