Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DateAdd and SamePeriodLastYear Returning Blank Values

Hi,

 

I'm new to PowerBI and I'm trying to create a measure which calculates the sales for the same time period as last year (and for last week so I'd rather use the dateadd function).

 

I'm pulling through a lot of data so I'm summarised my table to week starting date and I haven't created the generic date table within PowerBI so I'm not sure if these are causing any problems.

 

When I do the formulas it's returning blank values (s/s below of dummy data).

 

I'd upload the pbix file but I'm not sure how. The formulas I'm using are:

 

Sales LY Test1 = CALCULATE(SUM(Sheet1[Sales]), SAMEPERIODLASTYEAR(Sheet1[Time Period]))

Sales LY Test2 = CALCULATE(SUM(Sheet1[Sales]), DATEADD(Sheet1[Time Period], -364, DAY))

 

 

PowerBI Blank.PNG

 

Any help would be appreciated.

 

1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

Hi @Anonymous ,

 

If you have a date table, your measure is correct, but there is a little detail, maybe you ignore, please refer the following steps,

 

1. Create a one-to-many relationship between your table and date table.

 

Date1.jpg

 

2. Then we create a measure like yours.

 

Sales LY Test1 = CALCULATE(SUM('Table'[Sales]), SAMEPERIODLASTYEAR('Date'[Date]))

 

3. At last we put the ‘Date’[date] to the visual, the result like this,

 

 date2.jpg

 

Or if you don’t have a date table, we can create a new measure to meet your requirement.

 

Sales LY Test 2 = 
var _currentyear = MAX('Table'[Year])
var _lastdate = DATE(_currentyear-1,MONTH(MAX('Table'[Time Period])),DAY(MAX('Table'[Time Period])))
return
CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Time Period]=_lastdate))

 

date3.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Osama14
Frequent Visitor

Hi, I have the same problem and have tried multiple formulas but still showing blank. 

 

I have a Table having KPI values, and another one is a calendar table. i am trying to show last year value but no luck 

 

I have tried this: 

 

PREVLSV =
var _curYear = MAX('Date Dimension'[Year])
RETURN
CALCULATE(SUM('KPI'[Actual_Value]), FILTER(ALL('Date Dimension'), 'Date Dimension'[Year] = _curYear -1)) 
 
and this as well 

Last Year = CALCULATE(SUm('KPI'[Actual_Value]), DATEADD('Date Dimension'[Date].[Date]), -364, DAY))

Last Year = CALCULATE(SUm('KPI'[Actual_Value]),SAMEPERIODLASTYEAR('Date Dimension'[Date].[Date]))
 
I have done the relation between Data and KPI table it's always showing blank !! Can you advise 
Osama14_0-1636267268396.png

 

v-zhenbw-msft
Community Support
Community Support

Hi @Anonymous ,

 

If you have a date table, your measure is correct, but there is a little detail, maybe you ignore, please refer the following steps,

 

1. Create a one-to-many relationship between your table and date table.

 

Date1.jpg

 

2. Then we create a measure like yours.

 

Sales LY Test1 = CALCULATE(SUM('Table'[Sales]), SAMEPERIODLASTYEAR('Date'[Date]))

 

3. At last we put the ‘Date’[date] to the visual, the result like this,

 

 date2.jpg

 

Or if you don’t have a date table, we can create a new measure to meet your requirement.

 

Sales LY Test 2 = 
var _currentyear = MAX('Table'[Year])
var _lastdate = DATE(_currentyear-1,MONTH(MAX('Table'[Time Period])),DAY(MAX('Table'[Time Period])))
return
CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Time Period]=_lastdate))

 

date3.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , In such case use  date table , example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"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))
Week year behind = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

Anonymous
Not applicable

Thanks for your response, I haven't had time to watch your webinar yet but I'm not sure I see the relevance of 'Date'[Date].

I've added the date table in but it's still not giving the answer I'd expect.

 

I've updated the formulas to:

 

Sales LY Test1 = CALCULATE(SUM(Sheet1[Sales]), SAMEPERIODLASTYEAR('Date'[Date]))

Sales LY Test2 = CALCULATE(SUM(Sheet1[Sales]), DATEADD('Date'[Date], -364, DAY))

 

PowerBI Blank.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.