Need a different YTD formula in DAX for Power BI - standard YTD doesn't work

Question

I need a dax measure that first calculates the monthly net difference in % of select clients THEN adds up the YTD total on a month by month basis.

The standard YTD function in DAX is not working for this calculation.

I don't understand why it is not working, but I think it is because I want a YTD for the MONTHLY calculation on net change - as it appears in my visual (rather than the cumulative YTD from daily changes).

Here is my current YTD formula that is not working:

YTD of % Change =
TOTALYTD ( [% Change], 'DateTable'[Date].[Date] )

I am using other YTD calculations in the same visual with the same table data and they compute a correct answer so I don't think that the problem is with my Date table or the connection.

Here is the Dax for my % Change measure (this measure is fine. It gives the monthly results that I expect which is the month over month difference) just in case it helps...

% Change =
VAR __PREV_MONTH =
    CALCULATE ( [% Of Base Group], DATEADD ( 'DateTable'[Date].[Date], -1, MONTH ) )
RETURN
    [% Of Base Group] - __PREV_MONTH

My monthly data is SNAPSHOT style data with a full data set for every month so I don't normally calculate totals or counts across months (since each month already has the full count of client and totals) - I only keep it in one table so that I can calculate the month to month differences.

Results that I currently receive (i.e. the wrong ones) are shown below along with the expected result

Base field Expected Result What I get Year Month % Change YTD YTD of % Change 2018 Jan -0.10% -0.10% -0.10% 2018 Feb -0.44% -0.55% -0.28% 2018 Mar -0.09% -0.64% -0.21% 2018 Apr 0.06% -0.58% -0.14% 2018 May 0.87% 0.29% 0.06% 2018 Jun -0.26% 0.03% 0.01% 2018 Jul 0.10% 0.13% 0.02% 2018 Aug 0.08% 0.21% 0.03% 2018 Sep 0.00% 0.21% 0.02% 2018 Oct -0.08% 0.13% 0.01% 2018 Nov -0.18% -0.06% -0.01% 2018 Dec -0.77% -0.83% -0.07% 2019 Jan -0.26% -1.09% -0.26% 2019 Feb -0.44% -1.53% -0.35%

Kerry Kerry · Accepted Answer · 2019-04-01T14:08:02

I found a solution (although it is not pretty) that gives me the results I am looking for.

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 (to calculate that first net change) by using value(YYYYMM)-89.

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
    )

I still don't know why the regular YTD function did not work and cannot figure out how the regular YTD function calculated the numbers that it did, but for now my problem is solved as I have this workaround.

Need a different YTD formula in DAX for Power BI - standard YTD doesn't work

2 Answers