Forum Discussion

SarahESkells's avatar
SarahESkells
Icon for Helper I rankHelper I
4 years ago
Solved

MTD and YTD table

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." 

WhoMTDQTDYTD
Person 13001,2674,967
Person 2101503,720

 

Any help will be appreciated

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

2 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    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.