Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

YTD vs PYTD

To start with my calendar Dimention looks like this because we have a slicer which limits to current month

 

Dates_Dim =
GENERATE (
CALENDAR ( DATE ( Year(TODAY()), 0, 1 ), DATE ( Year(TODAY()),MONTH(TODAY()),31)),
VAR currentDay = [Date]
VAR day = DAY( currentDay )
VAR month = MONTH ( currentDay )
VAR year = YEAR ( currentDay ) var Month_name = FORMAT([Date], "MMM")
RETURN ROW (
"day", day,
"month", month, "Month_name",Month_name,
"year", year )
)
 
Now i have done the YTD and MTD measures using the formula 
 
YTD_BUD = TOTALYTD(Sum(Table1[Budget]),Dates_Dim[Date])
YTD_FCST = TOTALYTD(Sum(Table1[FCST]),Dates_Dim[Date])
YTD_MEA = TOTALYTD(Sum(Table1[MEA]),Dates_Dim[Date])
 
Variance = Table1[YTD_MEA] - Table1[YTD_FCST]
Percentage = divide (Table1[YTD_MEA] , Table1[YTD_FCST])
 
All above works as expected.
 
Now i want to do PYTD vs YTD
 
PYTD_MEA  = CALCULATE([YTD_MEA],SAMEPERIODLASTYEAR('Dates_Dim'[Date]))
PYTD_FCST = CALCULATE([YTD_FCST],SAMEPERIODLASTYEAR('Dates_Dim'[Date]))
 
Variance = Table1[PYTD_MEA] - Table1[PYTD_FCST]
Percentage = divide (Table1[PYTD_MEA] , Table1[PYTD_FCST])
 
Above is not working. Need urgent help to fix it.
Need Help
Show and Tell
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    Hello Anonymous 

    The date table generated from your code in Dates_Dim runs from 12/1/2018 - 10/31/2019.  Using time intelligence functions with a date table that does not cover the full years can give you wrong answers.  Try changing your Dates_Dim code to the following.

    Dates_Dim = 
ADDCOLUMNS(
    CALENDAR ( 
        DATE ( YEAR ( TODAY() ) - 1, 1, 1 ), 
        DATE ( YEAR ( TODAY() ), 12, 31 ) 
    ),
    "Day", DAY ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Month_name", FORMAT ( [Date], "MMM" ),
    "Year", YEAR ( [Date] )
)

3 Replies

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

    Is PYTD Measure not working of variance is not working.

    1. Hope date table is marked as a date table in the model view.

    2. Dates are continuous. In case dates are not continuous(means some dates are missing) SAMEPERIODLASTYEAR might not work properly 

    3. Create a formula like below and check are you getting correct values

    On the Fly YTD LYTD Change % = 
Var _last_year= (maxx('Date',ENDOFYEAR(DATEADD('Date'[Date Filer],-12,MONTH))))
Var   _This_year=(maxx('Date','Date'[Date Filer]))
Var _min_last_year= (MINX('Date',STARTOFYEAR(DATEADD('Date'[Date Filer],-12,MONTH))))
Var   _min_This_year=(Minx('Date',STARTOFYEAR('Date'[Date Filer])))


Var _last_year_val= CALCULATE(sum(Sales[Sales Amount]),(Sales[Sales Date])<=_last_year && (Sales[Sales Date]) >=_min_last_year)
Var   _This_year_val =CALCULATE(sum(Sales[Sales Amount]),(Sales[Sales Date])<=_This_year && (Sales[Sales Date]) >=_min_This_year)
return
(_This_year_val-_last_year_val)/_last_year_val*100
    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
    Thanks.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This solution did not work. Can someone provide me Dax how we could achieve this?

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

        Hello Anonymous 

        The date table generated from your code in Dates_Dim runs from 12/1/2018 - 10/31/2019.  Using time intelligence functions with a date table that does not cover the full years can give you wrong answers.  Try changing your Dates_Dim code to the following.

        Dates_Dim = 
ADDCOLUMNS(
    CALENDAR ( 
        DATE ( YEAR ( TODAY() ) - 1, 1, 1 ), 
        DATE ( YEAR ( TODAY() ), 12, 31 ) 
    ),
    "Day", DAY ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Month_name", FORMAT ( [Date], "MMM" ),
    "Year", YEAR ( [Date] )
)