YTD % for dynamic date periods

[Anonymous]

Hi all,

I'm struggling to show YTD % change based on a dynamic date filter.

I have a Calendar table that has:

Date, QuarterInCalendar, IsFirstDateOfYear

1/1/2019,   Q1 2019, 1

1/2/2019, Q1 2019, 0

9/14/2020, Q3 2020, 0    (Calendar goes up to most recent date in dataset)

I'm using QuarterInCalendar as my date filter to show a YTD total % as of the last available date in the period.

I'm using 5 measures:

AUM Total = CALCULATE(SUM(DailyAssets[AUM]),LASTDATE('Calendar'[Date])) 

(I'm using LASTDATE here because otherwise, I'll get a daily sum of assets from a dated series, which isn't what I want; I want the amount as of the latest date.)

AUM FirstOfYear = CALCULATE([AUM Total],'Calendar'[FirstDayOfYearYN]=1)

(This only returned records for the first day of year, but I wanted that value filled in for all other dates. Thus my next measure.)

AUM FirstOfYear Fill =

VAR y = SELECTEDVALUE('Calendar'[Date])

RETURN

CALCULATE([AUM FirstOfYear], TOPN(2, FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]<=y && NOT (ISBLANK([AUM FirstOfYear])))))

(I looked this one up. The example used TOPN(1,...) but my dataset covers 2 calendar years, so I have 2 "first days of year", and using TOPN(1,...) filled all the rows with the value from 1/1/2019, including rows into 2020.)

Finally, I use these last two measures to get YTD %:

AUM YTD Diff = IF(ISBLANK([AUM Total]), BLANK(), IFERROR([AUM Total] - [AUM FirstOfYear Fill],BLANK()))

AUM YTD % = IF(ISBLANK([AUM Total]), BLANK(), IFERROR([AUM YTD Diff]/[AUM FirstOfYear Fill],BLANK()))

The data in my resulting table is correct, but for some reason I'm not getting totals for AUM FirstOfYear Fill or AUM YTD %:

And when I try to slice dynamically based on QuarterInCalendar, I get blanks:

All this, just to show YTD % ! Feels like it should be easier. 

1) Is there an easier way to get to my desired end-state, showing YTD % as of latest date based on QuarterInCalendar slicer?

2) If not, how do I get these measures to show records for QuarterInCalendar instead of specific dates?

Any help would be tremendously appreciated. Thank you!!!

[amitchandak]

@Anonymous , Try like

AUM YTD Diff = [AUM Total] - [AUM FirstOfYear Fill]
AUM YTD % = divide([AUM YTD Diff],[AUM FirstOfYear Fill],0)

Of

AUM YTD Diff = sumx(Values(Date[Date]),[AUM Total] - [AUM FirstOfYear Fill])

AUM YTD % = Average(Values(Date[Date]), divide([AUM YTD Diff],[AUM FirstOfYear Fill],0))

[Greg_Deckler]

@Anonymous This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Anonymous]

Thanks for the links. I tried creating a new SUMMARIZE measure to group by QuarterInCalendar, but still got blanks. Here's my measure; maybe I did something wrong?

AUM YTD % 2 =

VAR _table = SUMMARIZE(DailyAssets,'Calendar'[QuarterInCalendar],"AUM YTD %",[AUM YTD %])

RETURN

IF(HASONEVALUE('Calendar'[QuarterInCalendar]),[AUM YTD %],SUMX(_table,[AUM YTD %]))

[amitchandak]

@Anonymous , In place of summarize sumx(values() ,[] ) , with YTD

[Anonymous]

I tried your suggestion as new measure AUM YTD % 3 and got closer, in that I now see a total, but it shows as 0

AUM YTD % 3 = DIVIDE([AUM YTD Diff],[AUM FirstOfYear Fill],0)

Also, I'm unsure what you meant by changing SUMMARIZE to SUMX. I tried doing that, but got a syntax error:

[Anonymous]

Updated measure. Am I any closer?

AUM YTD % 2 = VAR _table = SUMMARIZE('Calendar','Calendar'[QuarterInCalendar],"_value",[AUM YTD %])

RETURN

IF(HASONEVALUE('Calendar'[QuarterInCalendar]),[AUM YTD %],SUMX(SUMMARIZE(VALUES(_table),[_value]), ... ??

Also, for my learning: if the end goal is to show a YTD %, what exactly would I be summing via SUMX? I don't want to sum the percents, but show YTD % as of the latest date in the QuarterInCalendar (period) selected.

[amitchandak]

@Anonymous , First try this, have YTD measure without any has one values. Have slicer on the page to select a date in this year or Put a page or visual level filter and use a relative date filter say the last 5 years including today.

YTD needs a date to stop. Now it will get this year's date. else it will go till the end date of the calendar and calculate the grand total. Grand total in calculated again. No month/qtr/year in the row, so the last date available on-page for datesytd

[Anonymous]

I tried adding specific dates to the visual filter so the calc knew where to stop when selecting a QuarterInCalendar, but that did not work. It seems any restriction at all on date prevents my AUM YTD % from appearing. See below; the bottom table has the records appear, but has no date filter. The top table has a visual-level filter for specific end-of-quarter dates, but as soon as I select a date, the value is gone.

[Anonymous · Accepted solution]

Finally got this. Posting answer in case it helps someone else.

Note for Measure 1 below, the LASTDATE is used at the end because this is a rolling total that I don't actually want to SUM. Maybe there's a better way to accomplish this than what I have for Measure 1, but hey, it works.

Measure 1 (Total Current Value) = CALCULATE(SUM(Table[Value]),LASTDATE('Date'[Date]))

Measure 2 (First Value of each Year) = CALCULATE([Measure 1],STARTOFYEAR('Date'[Date])) 

Measure 3 (Difference between Current Value and Start-of-Year value over multiple years) = IF(ISBLANK([Measure 1]), BLANK(), IFERROR([Measure 1] - [Measure 2],BLANK()))

Measure 4 (Dynamic YTD %) = IF(ISBLANK([Measure 1]), BLANK(), IFERROR(TOTALQTD([Measure 3]/[Measure 2],'Date'[Date]),BLANK()))

Changes made:

• Was able to get my "First Value of each Year" (Measure 2) to fill every row for every date. This was key. This allowed me to remove my old "AUM FirstOfYear Fill" measure. I did this based on this change:

  • Measure 2 = CALCULATE([Measure 1],'Calendar'[FirstDayOfYearYN]=1) STARTOFYEAR('Date'[Date])) 

•  The above step allowed me to pull a value for YTD %, but without a date filter, I was showing a sum of percents, when I wanted instead to show the most recent percent. To show most recent percent for my period, I made this change:

  • Measure 4 = IF(ISBLANK([Measure 1]), BLANK(), IFERROR([AUM YTD Diff]/[AUM FirstOfYear Fill]TOTALQTD([Measure 3]/[Measure 2], 'Date'[Date]),BLANK()))

Cheers!