YTD value until selected month not working

[iW1DOV]

Hi all, 

I need to show in a chart the YTD value from beginning of the year until the selected month in a single select slicer.  That is, the user wants to select ONLY the end period month in the slicer, like this: 

The above picture shows the desired result; however  my chart (a matrix in this case for clarity)  always diplays the value (which is also wrong, moreover) for the selected month only, like this: 

My measure currently looks like this: 

PPV YTD = 
    CALCULATE
    (
        SUM('Procurement'[PPV]),
        FILTER
        (
            ALL('Calendar'),
            'Calendar'[Date] <= MAX('Calendar'[Date]) 
                && YEAR('Calendar'[Date]) = YEAR(MAX('Calendar'[Date]))
        )
    )

But I tried also variants with TotalYTD, datesYTD and so on, to no avail. 
My model looks like this 

(this is a sample model with dummy data, of course) ; the Calendar table is correctly marked as a Date table on the Date field, and it currently contains all the days between 2023-01-01 and 2024-12-31. 

I think I did this before a trilion times, but now I am not able to accomplish the task. This is driving me nuts... 
Can anyone help me to find the error, pleaase? 

If needed I have a sample file with dummy data, but I don't know how to attach it here. 

Thank you in advance, 

Gianfranco 

[Anonymous · Accepted solution]

Thanks for the reply from divyed , please allow me to provide another insight: 
Hi @iW1DOV ,

I created some data:

You need to create a date table that is not connected as a slicer.

When your slicer is the same table field or has a join with a field in visual, it will filter the data in visual when the slicer is selected.

Here are the steps you can follow：

1. Create calculated column.

Date_Table =
DISTINCT('Table'[Date])

2. Create measure.

Measure =
var _selectyear=SELECTEDVALUE('Date_Table'[Date].[Year])
var _selectmonth=SELECTEDVALUE('Date_Table'[Date].[MonthNo])
var _mindate=DATE(_selectyear,1,1)
var _maxdate=EOMONTH(MINX(FILTER(ALL('Table'),YEAR('Table'[Date])=_selectyear&&MONTH('Table'[Date])=_selectmonth),[Date]),0)
return
SUMX(
    FILTER('Table',[Date]>=_mindate&&[Date]<=_maxdate),[Value])

3. Result:

Best Regards,

Liu Yang

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

[divyed]

Hello @iW1DOV ,

You can try below dax, modify table and field names

YTD_Value =
VAR SelectedYearMonth = SELECTEDVALUE(DateTable[Year-Month])
VAR SelectedYear = YEAR(SELECTEDVALUE(DateTable[Date]))
VAR SelectedMonth = MONTH(SELECTEDVALUE(DateTable[Date]))

RETURN
CALCULATE(
SUM(DataTable[Value]),
FILTER(
ALL(DateTable),
DateTable[Year] = SelectedYear &&
DateTable[Date] <=
MAXX(
FILTER(DateTable, DateTable[Year-Month] = SelectedYearMonth),
DateTable[Date]
)
)
)

I hope this helps.

Did I answer your query ? Mark this as solution or give a thumbs up if this helps. Kudos are appreciated.

Cheers

[iW1DOV]

I tried, but it does not work, the formula returns nothing, regardless there is a selection in the calendar slicer or not. Even selecting all calendar the measure returns no values. 

BTW "DateTalbe" =  Dim Calendar table & "DataTable" = facts table, right? 

[iW1DOV]

I uploaded a sample report file here: Sample report file 
Hope the link works... 

Gianfranco