Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

 

 

 

Any help would be appreciated.

 

Tutorial Requests
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    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.

     

     

    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,

     

     

     

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

     

     

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

       

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.

     

     

    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,

     

     

     

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

     

     

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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