Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Prior/Last Year DAX same as Current Year

Good day   Can someone please advise where I am going wrong trying to calculate prior year values in my three dax-formulas below?   These three Sales LY should generate the same value, but it is ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Try this formula. 'Table' is data table and 'date' is date table. I create a year column in the date table.

    Measure 2 = CALCULATE(SUM('Table'[value]),FILTER('Table',YEAR('Table'[date])=SELECTEDVALUE('date'[year])-1))

     

     

    Best Regards,

    Jay

nvprasad's avatar
nvprasad
Icon for Solution Sage rankSolution Sage
5 years ago

Hi,

 

Continuous dates are required for all time intelligence calculations. Kindly use the date or calendar table to pass date.

 

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

nvprasad , You need to use a date table in all such cases.

refer 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 year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year 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))

 

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

 

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 :radacad sqlbi My Video Series Appreciate your Kudos.

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi amitchandak 

     

    Dim_Time is my date table. Can you see something wrong with it? What should I check on it?

     

     

    I converted the DAX formulas to pass Dim_Time and not Dim_AccountingPeriod since Dim_Time has continuous dates, but I get the following:

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi amitchandak 

     

    I used these two of your suggestions for Last Year Sales, but they dont work.

     

    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

    Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

     

    What does "12/31" mean? the syntax seems wrong. Visual studio gives an error when used verbatim.