Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Prior YTD

Hi, 

 

I want to create Prior YTD calculation so I made two versions of it. Not sure which one to go forward with because both are giving different answers ...

 

Prior YTD = calculate (
[Total Netsales],SAMEPERIODLASTYEAR('CDD Fiscal Dates'[date]))
 
 and another formula 
 
prior_YTD_2 = CALCULATE([Total Netsales],PREVIOUSYEAR(DATESYTD('CDD Fiscal Dates'[date])))
 
both are giving me different Prior YTD. Could anyone help me with this confusion please. 
 

 

These are my two Prior YTD calculation both giving different answer 


 

Need Help

2 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    According to your description, I create a sample, and it get the incorrect result as yours. 

    Here's my solution, modify the Prior YTD formula to :

    Previous Year YTD =
CALCULATE ( 'Table'[Current YTD], SAMEPERIODLASTYEAR ( 'Table'[Date] ) )

    Here, directly reference to measure [Current YTD], not Net Sales.

    Get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    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, 

       

      Thanks for the reply, I have few more confusions.

      1. so my date table has a YTD flag , so if I switch on the flag the calculation looks different. but the total value of both way of calculating is the same. 

      2. In the similar way my YOY growth and variance should also have a different calculation ? 

       

      YOY Netsales Growth% =
      VAR PYsales =
      calculate([Total Netsales],DATEADD('CDD Fiscal Dates'[date],-1,year))
      RETURN
      DIVIDE(([Total Netsales]-PYsales),PYsales)      or 
       
      Total YoY% =
      IF(
          ISFILTERED('CDD Fiscal Dates'[date]),
          ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
          VAR __PREV_YEAR =
              CALCULATE(
                  [current YTD],
                  DATEADD('CDD Fiscal Dates'[date].[Date], -1, YEAR)
              )
          RETURN
              DIVIDE([current YTD] - __PREV_YEAR, __PREV_YEAR)  again both of these are giving different answers