Show only curren year month values

[nachoroncero]

Hello,

This is an example of my dataset. I have values of everymonth since Jan-2017 to Dec-2019 with this shape.

I would like to use a chart line showing only YTD data.

Example:

I have a slicer where I select one specific month, if I choose that month, I want data from Jan. from that year to the selected month only. I choose May 2018, I only want to see data from Jan.2018 to May 2018 in my chart, how can I do that?

Thank yo for your time

[moizsherwani · Accepted solution]

First create a calendar table by going to Modelling->New Table and paste the following

(you must ensure that the newly created calendar table is not connected to the data table which you can do by checking the Model view)

Calendar = 

var _fromYear=year(FIRSTDATE(DummyData[Date])) 
var _toYear=year(LASTDATE(DummyData[Date]))   

return
ADDCOLUMNS(
    CALENDAR(
                DATE(_fromYear,1,1),
                DATE(_toYear,12,31)
),

"Start of Month",DATE( YEAR([Date]), MONTH([Date]), 1)
)

Next create a measure 

SumValuesYTD =
VAR EndDate =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
VAR EndDateYear =
    YEAR ( EndDate )
VAR StartDate =
    DATE ( EndDateYear, 1, 1 )
RETURN
    CALCULATE (
        SUM ( DummyData[Values] ),
        FILTER ( DummyData, DummyData[Date] >= StartDate && DummyData[Date] < EndDate )
    )

For the slicer use the "Calendar" "Start of Month" Field and for the visual (table/graph) use the Data Field (see screenshot below).

Let me know if this helps

[nachoroncero]

Thank ou ver much for your time and solution. It works as expected!

[nachoroncero]

I am using this slicer.

[moizsherwani]

Which table is this Mes Seleccionado coming from, the same table as the data (fact table) or is there a seperate calendar table (dimension table) which it is from?

[nachoroncero]

from the same table, the one I showed above in the first message. It all bellongs to it.

I want to use the fecha column (Date in English) and the mensual column that contains the values I want to display for the specific dates

[moizsherwani]

@nachoroncero Are you able to create a new table (supporting calendar table) or a new column if the solution required it?

[nachoroncero]

Yes, I could

[moizsherwani]

@nachoroncero  are you using a calendar table to select the month or just selecting it from the data table itself?