MTD and YTD table

[SarahESkells]

Hello

I think this is a straightforward question but I am struggling to get the data to work for me.

I have a table of data, I have changed the layout to work for me so looks something like the below:

The data comes from a spreadsheet where the individual is required to input a value for each month, I want to create a table like the below but am struggling to get the measures to work as just get the error "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column." 

Who	MTD	QTD	YTD
Person 1	300	1,267	4,967
Person 2	10	150	3,720

Any help will be appreciated

[Ashish_Mathur]

Hi,

Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.  Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.  Create slicers for Year and Month name and select 1 year and month

Write these measures

MTD = sum(Data[Amount])

YTD = calculate([MTD],datesytd(calendar[Date],"31/12")

Hope this helps.

[v-yanjiang-msft · Accepted solution]

Hi @SarahESkells ，

According to your description, if you use the DATESMTD, DATESQTD,  DATESYTD functions in the MTD, QTD, YTD measures, it can't return correct result because these functions refer to a date column which doesn't exsit in your visual. Here's my solution, create three measures.

MTD =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER ( 'Table', EOMONTH ( 'Table'[Date], 0 ) = EOMONTH ( TODAY (), 0 ) )
)

QTD =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        'Table',
        YEAR ( 'Table'[Date] ) = YEAR ( TODAY () )
            && QUARTER ( 'Table'[Date] ) = QUARTER ( TODAY () )
    )
)

YTD =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER ( 'Table', YEAR ( 'Table'[Date] ) = YEAR ( TODAY () ) )
)

Get the correct result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.