How to calculate YTD of the % Change from a Distinct Count when using snapshot dataset

[Kerry_M]

The YTD function is not working for me for a very specific calcluation on a data set.

The YTD function works just fine on the same data for several different calculations so I think the problem is with my logic on this formula rather than the date field.

I am trying to calculate the YTD Total for the PERCENT CHANGE month by month of a set of records.

As you will see from the RESULTS example below. The standard YTD DAX code which normally works is producing some very weird results (the results I get are in the second last column of the table in red)

The table below is the result table I am trying to produce. I have used DAX Measures successfully to:

- do a distinct count of a select group of records,

- divide the select group by the base to get a %  of base

- calculate the difference month over month for the % of base to see the change 

BUT

when I try to add up all those month over month % changes, my YTD formula produced numbers that do not make any sense to me at all.  Can anyone explain what these numbers mean and or how to get the results I want? I think I need to isolate that monthly calculation in the DAX and then sum it YTD but I am not sure how.

The desired result is in the last column of the table (I calculated it in Excel as I cannot figure out how to do it in DAX)

It should be noted that this is a weird data set....each month contains an entire snapshot of the data so I would not usually add or calculate across months.  To deal with this issue in other YTD calculations I made sure that my key column for distinct counts is a combo of ID and Month - making a unique key for each client each month. 

			=CALCULATE(DISTINCTCOUNT(GroupTable[ID]),FILTER(GroupTable,GroupTable[Options]>0))	=Divide([Select Group],[Base Group],blank())	=  VAR __PREV_MONTH =   CALCULATE(    [% Of Base Group],    DATEADD('DateTable'[Date].[Date], -1, MONTH)   )  RETURN      [% Of Base Group] - __PREV_MONTH	=TOTALYTD( [% Change in the Select Group], 'DateTable'[Date].[Date] )
Year	Month	Base Group	Select Group	% Of Base Group	% Change in the Select Group	YTD Total of % Change in Select Group (WRONG RESULT)	DESIRED RESULT
2018	January	8,179	2,154	26.34%	-0.10%	-0.10%	-0.10%
2018	February	8,300	2,149	25.89%	-0.44%	-0.28%	-0.55%
2018	March	8,345	2,153	25.80%	-0.09%	-0.21%	-0.64%
2018	April	8,349	2,159	25.86%	0.06%	-0.14%	-0.58%
2018	May	8,054	2,153	26.73%	0.87%	0.06%	0.29%
2018	June	8,293	2,195	26.47%	-0.26%	0.01%	0.03%
2018	July	8,327	2,212	26.56%	0.10%	0.02%	0.13%
2018	August	8,350	2,225	26.65%	0.08%	0.03%	0.21%
2018	September	8,369	2,230	26.65%	0.00%	0.02%	0.21%
2018	October	8,379	2,226	26.57%	-0.08%	0.01%	0.13%
2018	November	8,403	2,217	26.38%	-0.18%	-0.01%	-0.06%
2018	December	8,571	2,195	25.61%	-0.77%	-0.07%	-0.83%
2019	January	8,606	2,182	25.35%	-0.26%	-0.26%	-1.09%
2019	February	8,767	2,184	24.91%	-0.44%	-0.35%	-1.53%

[v-juanli-msft]

Hi @Kerry_M 

Do you have data monthly level or daily level?

Best Regards

Maggie

[Kerry_M]

The data all uses the same month end date - so I guess that means monthly level

[Kerry_M · Accepted solution]

I still have not figured out why the YTD function does not work nor can I figure out how Power BI is calculating the results that it shows with the standard YTD function, but I did manage to create my own - very convoluted formula which produces the desired results. To do this, I basically subtracted the January of current year [% of Base Group] value from the current [% of Base Group] value then added on the net change that had occurred in January. I have a column in my date table that shows YYYYMM as a value so I was able to filter to December of the previous year by using value(YYYYMM)-89.

Here is what I wrote - I am sure there are many ways to write something cleaner and more efficient - but at least this one gives me the results I am looking for:

YTD Total of % Change in Select Group  = 
VAR FirstMonthofCurrentYear = VALUE(Min(DateTable[Date].[Year])&"01")
VAR LastMonthofPrevYear = FirstMonthofCurrentYear - 89
VAR JanPercentofBaseGroup =
		CALCULATE(
			[% Of Base Group],DateTable[YearMonthOrder]= FirstMonthofCurrentYear,ALLEXCEPT(DateTable,DateTable[Date])
		)
VAR JanChangeinPercent =
	JanPercentofBaseGroup - CALCULATE(
[% Of Base Group],DateTable[YearMonthOrder] = LastMonthofPrevYear,ALLEXCEPT(DateTable,DateTable[Date])
)
Return   
If (
[% Of Base Group]=blank(),
BLANK(),
[% Of Base Group]- JanPercentofBaseGroup + JanChangeinPercent
)