YTD and Prior Years to Date Measure

[CatManKuhn]

Hi all,

I am strugging with a YTD measure. I would like to show a chart with Year as my legend and Month as my X-axis. I am struggling to remove the values for months that are greater than the month of the max date in my date table (essentially today). My goal is to remove the horizontal lines on the chart. I should see dots for the month of January since we are 4 days into the month. 

Here is my current DAX measure:

External Hires YTD = VAR MaxDay =
    CALCULATE (
        MAX ( 'Date'[Day of Year] ),
        ALL( 'Date' ),
        'Date'[CurrentDayOffset] = 0
    )
RETURN
    TOTALYTD (
        CALCULATE ( [External Hires], 'Date'[Day of Year] <= MaxDay ),
        'Date'[Date]
    )

Here is the visual and a matrix to better represent what I want to omit. I do not want the YTD values for prior years to repeat into "future" months.

Here is the current matrix:

Month	2016	2017	2018	2019	2020	2021
January	28	133	144	106	50	215
February	28	133	144	106	50	215
March	28	133	144	106	50	215
April	28	133	144	106	50	215
May	28	133	144	106	50	215
June	28	133	144	106	50	215
July	28	133	144	106	50	215
August	28	133	144	106	50	215
September	28	133	144	106	50	215

Here is what the ideal outcome:

Month	2016	2017	2018	2019	2020	2021
January	28	133	144	106	50	215

I'll of course play around with new visuals as the dots are not the best when there is only one month in the year so far. 

Thanks in advance!!

[AlexisOlson]

Does adding KEEPFILTERS help?

[...]
CALCULATE ( [External Hires], KEEPFILTERS ( 'Date'[Day of Year] <= MaxDay ) ),
[...]

[CatManKuhn]

Thanks @AlexisOlson. I tried this, but no change. Really appreciate you for taking a look at this.

[AlexisOlson]

OK. I think there's a hidden ALL inside the TOTALYTD since that's just syntactic sugar where

TOTALYTD ( [Measure], 'Date'[Date] )

is actually equivalent to this:

CALCULATE (
    [Measure],
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
            && YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) )
    )
)

I'd recommend eliminating TOTALYTD entirely and trying something like this:

External Hires YTD =
VAR MaxDay =
    CALCULATE (
        MAX ( 'Date'[Day of Year] ),
        ALL ( 'Date' ),
        'Date'[CurrentDayOffset] = 0
    )
VAR MaxMonth =
    CALCULATE (
        MAX ( 'Date'[Month] ),
        'Date'[Day of Year] = MaxDay
    )
RETURN
    CALCULATE (
        [External Hires],
        'Date'[Day of Year] <= MaxDay,
        KEEPFILTERS ( 'Date'[Month] <= MaxMonth ),
        VALUES ( 'Date'[Year] )
    )

[VahidDM]

Hi @CatManKuhn 

Can you post sample data as text?

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

[CatManKuhn]

Thank you! I have updated my post.

[v-easonf-msft · Accepted solution]

Hi, @CatManKuhn 

You can create a measure as below and apply it to 'Filters on this visual'.

filter = IF(MONTH(TODAY())<MONTH(MAX('Table'[Date])),0,1)

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason

[CatManKuhn]

I really like this method. Is it possible to revise this to the day level rather than month? For example, since today is February 1st I would want to show the YTD hires for all years as of 2/1.