Calculate quarter as difference between YTD months

[ribaldo]

Hi all.

I've data stored in my database as YTD values, and YTD data are not a sum of months.

I'm looking for a way to calculate quaters not as an aggregation of months but as a difference between YTD values of the last month of the quarter.

Fo example:

					Expeted Result
Year	Quarter	Month	Sales YTD Amt	Sales Amt
2021		January	10	10
		February	23	13
		March	34	12
	Q1			35	34
		April	47	11
		May	60	14
		June	73	13
	Q2			38	39

Any ideas?

[ribaldo]

@amitchandak , I've some trouble implementing your solution. I'm using tabular with compatibility level 1400, and don't have isinscope function. I tried hasonevalue, but without success.

[amitchandak]

@ribaldo , You need a measure like this

if(isinscope(Date[Qtr]) && Not(isinscope(Date[Month])) , [sales YTD] - calculate([sales] -datesmtd( Date[Date])), blank())

Assuming you are using date table and time intelligence

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.