Fiscal YTD from YYYY-MM format

[Anonymous]

Hello, we have table with date stored in YYYY-MM format, I'd like to find out a way how to make YTD function based on this record. We also have fiscal year starting in March so 2018-01 is March 2018. Any ideas how to format DAX function?
Thanks

[v-piga-msft · Accepted solution]

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

[Anonymous]

Thanks a lot. It's indeed in text format, but your solution work.
MV