Data being excluded

Hi Anonymous,

From your description, it seems data in table from Excel contains data from 01/01/15 to 10/01/16, while data in table from SQL Server contains data from 01/01/15 to 10/01/16. You merge those two tables use Merge Queries feature in desktop and you want to display YTD and YTD in last year same period, right?

In your scenario, you can create a calendar table from the merged table:

Date =
CALENDAR (
    EOMONTH ( MIN ( 'Merge1'[Date] ), -1 ) + 1,
    EOMONTH ( MAX ('Merge1'[Date] ), 0 )
)

As the SQL table has the consecutive date column, you can build a relationship between the calendar table and merged table use this date column. Then create measures below:

YTD for archive =
IF (
    MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( 'Merge1'[Date] ), ALL ( 'Merge1' ) ),
    CALCULATE ( SUM ( 'Merge1'[Archive.Value] ), DATESYTD ( 'Date'[Date] ) )
)

YTD LY for archive =
    CALCULATE (
        [YTD for archive],
        SAMEPERIODLASTYEAR (VALUES ( 'Date'[Date] )
                ))

You can download attached .pbix to have a look.

Reference:
YTD last year DAX

Best Regards,
Qiuyun Yu

Thanks v-qiuyu-msft for your reply.

It helped me to work better with dates. I had created before a table with the dates for source, but not as calendar. About YTD and YTD LY, I had the idea of making them as part of the SUMMARIZED table columns and not as measures. Just as a personal decision.

Anyway, thanks a lot for your help. It clarify a lot on how to work with dates.

Have a great day!