CODEWITHSUNDEEP
powerbidax
Kerry
Kerry

Reputation: 121

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

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%

 

Upvotes: 1

Views: 1680

Answers (2)

Kerry
Kerry

Reputation: 121

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.

Upvotes: 2

StelioK
StelioK

Reputation: 1781

Assuming that your date table is a contiguous set of dates then something like the following should work just fine: 

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

Upvotes: 0

Related Questions