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 giving me the same value as the CY.

Sales LY:=
CALCULATE(
[Sales] ,
PARALLELPERIOD( 'SSAS V_DIM_AccountingPeriod'[ReportingDateEnd2],-1,YEAR))

Sales LY 2:=
CALCULATE(
[Sales] ,
SAMEPERIODLASTYEAR( 'SSAS V_DIM_AccountingPeriod'[ReportingDateEnd2]))

Sales LY 3:=
CALCULATE(
[Sales] ,
DATEADD( 'SSAS V_DIM_AccountingPeriod'[ReportingDateEnd2],-1,YEAR))

The Fact table that contains the Sales values contains the following keys to two date DIM tables, (DIM_AccountingPeriod and DIM_Time) :

- PeriodNo_SK,
- FinDateID

DIM_AccountingPeriod looks as follows and joins on PeriodNo_SK to PeriodNo_SK on the Fact table

DIM_Time looks as follows and joins on ID to FinTimeID on the Fact table

Need Help

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

6 Replies

nvprasad
Solution 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
  Super 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
    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
  Not applicable
  5 years ago
  Hi nvprasad
  
  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:
  
  on the Dim_Time table there are multiple companies/systems which has their own continuous set of dates - could this be an issue? In case it had an issue with multiple entries of a specific date (eg 1/1/2001 for company a, company b and company c), I deleted the other companies so that there is eg on one 1/1/2001 in the Dim_Time table. But that did not solve my problem.
Anonymous
Not applicable
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