topic Re: Calculate quarter as difference between YTD months in DAX Commands and Tips

Calculate quarter as difference between YTD months

ribaldo — Tue, 24 Aug 2021 07:54:30 GMT

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?

Re: Calculate quarter as difference between YTD months

amitchandak — Tue, 24 Aug 2021 08:16:50 GMT

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.

Re: Calculate quarter as difference between YTD months

ribaldo — Fri, 27 Aug 2021 12:15:05 GMT

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.