YTD vs PYTD

[Anonymous]

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.

[amitchandak]

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]

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

[jdbuchanan71 · Accepted solution]

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