Creating month to date metrics from YTD data source

[LynnM]

Hello,

I am literally pulling my hair out! My source data is YTD . I managed to create month only data but have run into an issue when I try to create a Full Year Budget. How can I show a full year budget that will not change with the date slicer?

Thanks in advance

[Anonymous · Accepted solution]

Hi @LynnM ,

I suggest you to to try REMOVEFILTER() or ALL() function in your code. You can create a Dimdate table with continuous date ,then create a relationship between 'Data'[Date] and 'DimDate'[Date] and create a measure.

Measure:

YTD remove filter =
CALCULATE (
    SUM ( 'Data'[Value] ),
    FILTER ( ALL ( 'Date' ), 'Date'[Year] = YEAR ( TODAY () ) )
)

Best Regards,
Rico Zhou

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

[Anonymous · Accepted solution]

Hi @LynnM ,

I suggest you to to try REMOVEFILTER() or ALL() function in your code. You can create a Dimdate table with continuous date ,then create a relationship between 'Data'[Date] and 'DimDate'[Date] and create a measure.

Measure:

YTD remove filter =
CALCULATE (
    SUM ( 'Data'[Value] ),
    FILTER ( ALL ( 'Date' ), 'Date'[Year] = YEAR ( TODAY () ) )
)

Best Regards,
Rico Zhou

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

[LynnM · Accepted solution]

Thank you! This worked.

[indkitty]

Hi Lynn,

If measure is on filter, remove filter.

Filtering Tables in DAX - SQLBI

Could you post sample data?