Dynamic YTD value as per user month slicer selection

[Anonymous]

Could you please advise on this?

I want to display the YTD  report. The value of YTD depends on the date in the slicer.

i'm using measure to calculate YTD measure  named report measure, data table has  report data vale and date table has  date month and year which has been used by slicer and date is connected with data table with column period .period is in date format

 Actual YTD =

CALCULATE ((

[Reporting value]),

FILTER (

ALL ( GL ),

GL[v] = "Actuals"

),

FILTER (

ALL ( GL ),

GL[Period]

>= SELECTEDVALUE('Date'[Date])

&& GL[Year]

<= SELECTEDVALUE ( 'Date'[Year] )

)

)

[grazitti_sapna]

Hey

This measure calculates the "Actual YTD" based on the "Reporting value" column. It applies two filters using the FILTER function:

The first FILTER filters the table GL to include only rows where the column GL[v] is equal to "Actuals".

The second FILTER filters the table GL based on the conditions:

GL[Period] should be greater than or equal to the selected date from the slicer (SELECTEDVALUE('Date'[Date])).

GL[Year] should be less than or equal to the selected year from the slicer (SELECTEDVALUE('Date'[Year])).

Thank you. Hope this will help

[v-jingzhang]

Hi @Anonymous 

If your slicer is using Month and Year columns without Date column, you need to use MAX instead of SELECTEDVALUE in your measure. You can try 

Actual YTD =
CALCULATE (
    [Reporting value],
    FILTER (
        ALL ( GL ),
        GL[v] = "Actuals"
            && GL[Period] <= MAX ( 'Date'[Date] )
            && GL[Year] = MAX ( 'Date'[Year] )
    )
)

Or you can try DATESYTD function. 

Actual YTD = CALCULATE ( [Reporting value], DATESYTD ( 'Date'[Date] ), GL[v] = "Actuals" )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Thanks @v-jingzhang  , this is giving me this error

  exact  meausre i tried

Actual YTD =

CALCULATE (

    [Reporting value],

    FILTER (

        ALL ( GL ),

        GL[v] = "actuals"

            && MONTH(GL[nPeriod]) <= MAX ( 'Date'[Month No.] ) && year(GL[nPeriod]) = MAX ( ' Date'[Year]

)))

[v-jingzhang]

Hi @Anonymous 

This error indicates that you are comparing values of two different data types. [nPeriod] and [Month No.] should be of the same data type. [nPeriod] and [Year] should be of the same type too. Please check the data types of the columns. 

Jing