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.
Hello All,
I'm having some trouble with the function SAMEPERIODLASTYEAR and slicer not displaying. I went through a few different stages and thought I would explain them to show you how I got to where I am now. The last stage is where I need help with this issue.
First I created a measure to show sales of the same period last year (January 2019 - March 2019).
Same Period LY Sales = CALCULATE(SUM(Table[Sales]), SAMEPERIODLASTYEAR(Table[OrderDate]))
Everything works fine, but when I select a manager from my slicer I get an error message along the lines of "...expects a contiguous selection when the date column is not unique...". After reading a few articles I came across this one that was selected as a solution:
http://www.kasperonbi.com/get-the-ytd-of-same-period-last-year-using-dax/
So I decided to test this out and create a new test measure.
Measure = CALCULATE(SUM(Table[Sales]),
DATESBETWEEN(Calendar[Date],
FIRSTDATE(DATEADD(Calendar[Date], -12, MONTH)),
Solved! Go to Solution.
I found a solution that seems to work.
In my date calendar I create a flag for future date column (below the formula is a video that was very helpful) :
Future Date Flag = IF(Calendar[Date] > TODAY(), "Future", "Past")
https://www.youtube.com/watch?v=XjVLaVLluYE&list=PL7GQQXV5Z8eczWqKFMDVoHMjTcpH3tgZm&index=3
I applied that as a filter for my visual card for Same Period Last Year Sales and only select "Past" (below is my measure for the value that the card displays):
Same Period Last Year = CALCULATE(SUM(Table[Sales]), SAMEPERIODLASTYEAR(Calendar[Date])
The visual works as expected even when I select a different manager. No more error...hopefully!
I found a solution that seems to work.
In my date calendar I create a flag for future date column (below the formula is a video that was very helpful) :
Future Date Flag = IF(Calendar[Date] > TODAY(), "Future", "Past")
https://www.youtube.com/watch?v=XjVLaVLluYE&list=PL7GQQXV5Z8eczWqKFMDVoHMjTcpH3tgZm&index=3
I applied that as a filter for my visual card for Same Period Last Year Sales and only select "Past" (below is my measure for the value that the card displays):
Same Period Last Year = CALCULATE(SUM(Table[Sales]), SAMEPERIODLASTYEAR(Calendar[Date])
The visual works as expected even when I select a different manager. No more error...hopefully!
This should work with date calendar. As long as join and correct date it used.
Can you try datesytd or totalytd with date calendar
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
I tried CALCULATE(SUM(Table[Sales]), DATESYTD(DATEADD(Calendar[Date], -1, YEAR), "12/31")) and as I suspected I got all of last year's sales and not last year's sales of the current period (January - March).
Seem like you have not selected any date .
Refer these
-- Force today filter
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"),'Date'[Date]<=today())
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"),'Date'[Date]<=date(year(today()-1),month(today()),day(today())))
-- with filter
YTD Sales 1= CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
Last YTD Sales 1 = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
--combine
final YTD = if(isfiltered(Date[Month Year]) || isfiltered(Date[Year]) || isfiltered(Date[Date]),[YTD Sales 1],[YTD Sales])
final LYTD = if(isfiltered(Date[Month Year]) || isfiltered(Date[Year]) || isfiltered(Date[Date]),[Last YTD Sales 1],[Last YTD Sales])
These measures only work if I have a date slicer (ex. month_year). That kind of defeats the purpose for me. I want to display YTD sales (January 1st, 2020 - March 8th, 2020) and Last Year YTD sales (January 1st, 2019 - March 8th, 2019).
Something needs to tell time intelligence where to stop. This can be a slicer. Stop your calendar today. Have a visual level filter <= today.
I think the first two should have worked without any date filter
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"),'Date'[Date]<=today())
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"),'Date'[Date]<=date(year(today()-1),month(today()),day(today())))
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 |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |