How to display YTD data on a column chart only up until a specified date,

[feeli]

I am stuck on what feels like should be a simple problem to solve,

I am producing a YTD graph using a very basic formula,

" YTD Actuals =

TotalYTD(

    [SUM Actuals],

    DimCalendar[Date]

) "

on the graph the dark green columns show the figures for each specific month, the light green is the YTD cumulative, because of some of the strange behaviour in months we are not yet reviewing ( September in this case ) I need to exclude the months post the current month in review, ie we are reviewing August so the YTD should not include the September onwards Data,

I have removed 

The report has a filter using a month slicer,  the data type for this filter is text so would be "August" so I have created a further column within the DimCalander that gives the End of the month in date type for the entire of the month as, so I can then use a SELECTEDVALUE to return the last day of the month in view (  I then attempted to use this to create a filter argument, ie DimCalander[Date] <= SELECTEDVALUE(DimDate[Month Date] )

I removed the month slicer interaction with the graph in view, so it looks that the YTD measure is performed for the entire 2023, so essentially I need to add back in a filter context that includes dates up until month in view.

I am relatively new to DAX so am still learning the basics and this is my first post on here so apologies if not clear etc😅 

The fact table on this report is;

"P&L"

this is related to the DimCalander as such 

P&L[Date] -> DimCalander[Date]

Not sure if the filter on date should be on the Dim or fact for the measure. 

Thanks

[feeli]

update .. 

The below code produces the desired result, however I have hardcoded the date value, I need this to be dynamic using the which is a result of the report month slicer "DATEVALUE(SELECTEDVALUE(DimCalendar[Month Date]))"

However if I swap out the hardcoded date for the dynamic function it will display figures until end of year again, 

If I change the calculated column of [Month Date] to output "DATEVALE("31/08/2023")" instead of using "ENDOF MONTH(DimCalander[Date])" ,  the dynamic date within the YTD measure again will work  

Attempt YTD =

VAR DateVAR = DATEVALUE("31/08/2023")

RETURN

CALCULATE(

    TotalYTD(

    [SUM Actuals],

    DimCalendar[Date]

    ),

    FILTER( DimCalendar,

    [Date] <= DateVAR

    )

)

it feels like the issue lies somewhere between the date output from the calculated column and the date used as the table filter, feel like the issue has been isolated but the resolution im still unsure of from here

[rubayatyasmin · Accepted solution]

@feeli 

Make sure that the format and datatype of the calculated column DimCalendar[Month Date] is consistent with the [Date] column of DimCalendar. If there are any discrepancies, it can affect filtering and comparisons. If possible, inspect a sample value of DimCalendar[Month Date] to see how it appears.

then let's update the DAX to,

Attempt YTD =
VAR SelectedEndDate = SELECTEDVALUE(DimCalendar[Month Date])

RETURN
IF(
ISBLANK(SelectedEndDate),
BLANK(),
CALCULATE(
TotalYTD(
[SUM Actuals],
DimCalendar[Date]
),
DimCalendar[Date] <= SelectedEndDate
)
)

[rubayatyasmin]

@feeli 

you need to modify the YTD Actuals measure to consider the selected month. We need to ensure that the YTD Actuals calculation is restricted up to the selected month's end date. This means we want the TotalYTD to be computed from the start of the year to the last date of the selected month.

try this dax 

YTD Actuals =
VAR SelectedMonthEndDate = SELECTEDVALUE(DimCalendar[Month Date])
VAR LastDateInContext =
IF(
SelectedMonthEndDate,
SelectedMonthEndDate,
MAX(DimCalendar[Date])
)
RETURN
CALCULATE(
TotalYTD(
[SUM Actuals],
DimCalendar[Date]
),
DimCalendar[Date] <= LastDateInContext
)

[feeli]

Thanks for the response, I think the logic of this solution is where my thought process was going, however still not getting the desired result,

it is producing the same result as my latest measure attempt

"Attempt YTD = 

CALCULATE(

    [SUM Actuals],

    DATESYTD(DimCalendar[Date]),

    DimCalendar[Date] <= DATEVALUE(SELECTEDVALUE(DimCalendar[Month Date]))

)"

It seems the filter on the graph is still overwritting the context 😩