Forum Discussion

aryank2000's avatar
aryank2000
Frequent Visitor
7 years ago

Prior MTD calculation

Hi,

 

I need to calculate MTDs for prior year. Here is my requirement. I would need to calculate counts of measure for full month for all months in prior year and only "to date" in corresponding month. For example, If today is July 20, 2019. I would need full months for all months of 2018. But for July 2018, I would need counts upto July 19, 2018. I tried the following but, this calculates full month for all months.

 

CALCULATE(TOTALMTD('FactTable'[SalesAmount],'DimDate'[Date] ) ,SAMEPERIODLASTYEAR('DimDate'[Date]))

 

and

 

CALCULATE( FactTable[SalesAmount], SAMEPERIODLASTYEAR(DatesMTD('DimDate'[Date])))

 

But no luck. Any idea? Thank you

 

 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

     

    [Your Measure] :=
var __today = today()
var __datesUpToToday =
	CALCULATETABLE(
		VALUES( DimDate[Date] ),
		DimDate[Date] <= __today,
		ALL( DimDate ) -- technically this is not necessary
	)
var __datesMtd =
	DATESMTD( DimDate[Date] )
var __intersection =
	INTERSECT(
		__datesMtd,
		__datesUpToToday
	)
var __lastYearSalesMtd =
	CALCULATE(
		SUM( 'FactTable'[SalesAmount] ),
		SAMEPERIODLASTYEAR( __intersection )
	)
return
	__lastYearSalesMtd

    Best

    Darek