CALCULATETABLE AND DATEADD not working together in variables to show YTD values in previous years

[JRHans09]

I am trying to write DAX in Calculation Groups to calculate YTD values from previous years combining CALCULATETABLE AND DATEADD.

The CALCULATETABLE portion is meant to hide future dates in YTD calculations.

Here is a screen shot with results and related DAX:

I have tried multiple code iterations, but cannot seem to get the combinations of variables to work correctly.

Code from above:

YTDPY1 =
VAR PY1 =
CALCULATE(
    SELECTEDMEASURE(),
    DATEADD(
        Dates[Date],
        -1,
        YEAR
    )
)
VAR YTDPY1 =
CALCULATE(
    PY1,
    DATESYTD( Dates[Date] )
)
VAR Result =
CALCULATE(
    YTDPY1,
    CALCULATETABLE(
        Dates,
        Dates[DatesUntilToday] = TRUE
    )
)
RETURN
Result

Any help would be greatly appreciated, along with an understanding of why the above codes do not work. I would guess it is related to filter context.

Thanks in advance for the help.

[JRHans09 · Accepted solution]

@amitchandak  and @Anonymous -

So, I have finally figured out the solution.

Please see DAX here for YTD 1 Year Ago, hiding any corresponding date in the previous year that would be greater than TODAY( ) -

VAR CurrentDates =
    FILTER (
        VALUES ( Dates[Date] ),
        Dates[Date] <= TODAY()
    )
VAR ResultPY1 =
    CALCULATE (
        SELECTEDMEASURE(),
        DATEADD(
            DATESYTD ( CurrentDates ),
            -1,
            YEAR
        )
    )
RETURN ResultPY1

Same DAX applied for PY2 and PY3 by adjusting the -1 to -2 and -3, respectively.

[amitchandak]

@JRHans09 , did not get the usages. But if you want to stop at today

YTD QTY forced=
var _max1 = today() //or maxx(allselected('Order'),'order'[Date])
var _max = format(_max,"MMDD")
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date', format('Date'[Date],"MMDD")<=_max))

YTD QTY forced=
var _max = today()
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)

[JRHans09]

@amitchandak - thank you for your suggestions. They do not work within my model, but I assume this is due to my calendar table, shown here:

Dates = 
CALENDAR(
    DATE( YEAR( MIN( Invoices[DateInvoice] ) ), 1, 1 ),
    DATE( YEAR( MAX( Invoices[DateInvoice] ) ), 12, 31 )
)

I created the table this way, per suggestion from Marco at sqlbi, suggesting this format for proper YTD calculations, and then the related calculated column to hide future dates, as already mentioned:

DatesUntilToday =
Dates[Date] <= TODAY()

The above DAX works outside of Calculation Groups, but my attempts within Calculation Groups have not worked, I assume because of the need to use variables, which I now understand declares a constant and any subsequent CALCULATETABLE filter has no effect on the variable calculation.

I tried all of your suggestions, but they either return blank or return the typical flat line within charts, extending to the end of the year. As mentioned above, I assume this is due to calendar table.

Is there another way to hide future dates, while still using my current calendar table?

[JRHans09]

@amitchandak 

Additional info/screenshot that might help:

My original DAX on top, and one of your suggestions on the bottom. The calculation is working in yours (not calculating past 'today'), but the typical flat line occurs for dates past today.

Is there a way to hide future dates, using your DAX?

Or using my DAX by applying the CALCULATETABLE outside of the first YTDPY1 variable?

[JRHans09]

I finally succeeded by embedding the DATESYTD within the DATEADD section, but the YTD is calculation is not hiding future dates. The values are showing through the end of the year, when they should stop showing after 'Today':

YTDPY1 =
VAR YTDPY1 =
CALCULATE(
    SELECTEDMEASURE(),
    DATEADD(
        DATESYTD( Dates[Date] ),
        -1,
        YEAR
    )
)
VAR Result =
CALCULATE(
    YTDPY1,
    CALCULATETABLE(
        Dates,
        Dates[DatesUntilToday] = TRUE
    )
)
RETURN
Result

[Anonymous]

Hi @JRHans09 

Try this, 1st create a date table and brinf yiur months in the canvas table, post this write the below measure.

Last Year YTD= var ly=calculate(total sales),dateadd(date,-1,year) return calculate(ly,filter(all(date),date<=max(date)

This will work buddy, kindly mark this as solution if this is fine with you.