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

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