DAX YTD Different end dates per channel - data dates consistency problem

MarkCBB

Here is an example of how I would do it.

I've used your Excel data posted earlier.

The logic is to iterate over the values of Channel using SUMX, and for each Channel determine the last date for which data exists, and if that date is earlier than the last selected date, cut off the PYTD calculation at that date.

Uploaded sample pbix here.

The PYTD UNITS measure looks like this:
(Note: I would have written this slightly differently using TREATAS, but there seems to be a bug with TREATAS in Power BI Desktop at the moment)

PYTD UNITS = 
SUMX (
    VALUES ( CHANNEL[CHANNEL] ),
    // Iterate over  CHANNELs
    VAR CalendarLastDateSelected = 
        MAX ( 'CALENDAR'[DATE] )
        // Last date selected on CALENDAR table
    VAR ChannelLastDateGlobal =
        CALCULATE ( MAX ( 'FACT'[DATE] ), ALLEXCEPT ( 'FACT', 'CHANNEL' ) )
        // Last date that exists in FACT for the current CHANNEL (ignoring any filters other than CHANNEL)
    VAR ChannelLastDateApplied =
        MIN ( CalendarLastDateSelected, ChannelLastDateGlobal )
        // The earlier of these is the date to apply for PYTD calculation  
    RETURN 
        CALCULATE ( 
            [TYTD UNITS],
            SAMEPERIODLASTYEAR( 'CALENDAR'[DATE] = ChannelLastDateApplied )
        )
)

You might also want to edit the measures so that YTD or PYTD values are hidden per channel for dates later than the maximum existing date, using the method on this page:

http://www.daxpatterns.com/cumulative-total/

Regards,

Owen