Need YTD and QTD to show previous months ONLY

[brittanyruffin]

Hello,

I am need to adjust my QTD and YTD formulas. I need to only calculate for previous months. Example, if I'm refreshing the file in July we are only reporting on January - June. Our data pulls in future month values which I don't want to see in these calcs.

These are my current formulas. These are intended for matrix tables that have no date axis. These are working great so I'd like to keep these and just add in the argument to start the calculation the month prior.

YTD Actual =

CALCULATE (

    SUM ( 'vCorporateGandA'[Amount] ),

    FILTER (

        ALL ( 'CalendarTable' ),

        YEAR ( 'CalendarTable'[Date] ) = YEAR ( TODAY () )

            && 'CalendarTable'[Date] <= TODAY ()

    ),

    'vCorporateGandA'[LedgerType] = "Actual"

)

QTD Actual =

CALCULATE (

    SUM ( 'vCorporateGandA'[Amount] ),

    FILTER (

        ALL ( 'CalendarTable' ),

        YEAR ( 'CalendarTable'[Date] ) = YEAR ( TODAY () )

            && QUARTER ( 'CalendarTable'[Date] ) = QUARTER ( TODAY ())

            && 'CalendarTable'[Date] <= TODAY ()

    ),

    'vCorporateGandA'[LedgerType] = "Actual"

)

[amitchandak]

@brittanyruffin , Try like

YTD =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),-1)
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Last Qtr =
var _max1 = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),-1)
var _max = eomonth(_max1,-1* if( mod(Month(_max1),3) =0,3,Month(_max1)))
var _min = eomonth(_min,-3)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))