Fiscal YTD from YYYY-MM format

Hi Anonymous,

 

If you want to calculate the YTD value, the data type of your date should be the Date or Date/Time.

 

Please check if your format of "YYYY-MM" is Date type. If it is you could try the formula below.

 

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

Or 

total_ytd = TOTALYTD(SUM('YTD'[Sales]),'YTD'[Date])

If your format of "YYYY-MM" is text type, you could follow the steps below.

 

1. Change your data type to Date and create a Calendar table with the formula;

 

Date2 = 
ADDCOLUMNS (
CALENDAR (DATE(2017,1,1), DATE(2018,10,31)),
"YearMonth", FORMAT ( [Date], "YYYY-MM" )
)

2. Create the relationship of the two tables and create the measure with the formula below.

 

Measure 2 = TOTALYTD(SUM('YTD'[Sales]),'Date2'[Date])

3. Create the visual below.

 

In addition, you could have a reference of my attachments.

 

Best Regards,

Cherry