Year to Date Calculation with no Calendar

[Signore_Ands]

I have seen multiple posts regarding Year to Date calculations, but they all seem to deal with cases where the data set includes a calendar. 

My data set carries just Month and Year values for the time period - not full dates.

I need to calculate Year to Date totals for my measures filtered based on the selection made in the Month slicer.

So, if I select May in the Slicer, the measure must calculate the total for the first 5 months of the year.

Then if I select February, it should calculate just for the first 2 months. 

So it must not be based on the current actual date (e.g. a TODAY() form of calculation) but rather only on what the slicers select.

How do I manipulate the calculation of the measure to take the slicer value into account?

[v-yetao1-msft · Accepted solution]

Hi @Signore_Ands 

I created a table may meet your needs ,you can refer to it .

Original Data:

Measure :

YTD_VALUES =

CALCULATE (

    SUM ( 'data'[Sales] ),

    FILTER (

        ALL ( 'data' ),

        'data'[Year] = SELECTEDVALUE ( 'data'[Year] )

            && 'data'[Month] <= SELECTEDVALUE ( 'data'[Month] )

    )

)

Put the measure in a card visual and use field ‘Month’ as a slicer ,the final result is as shown :

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

[Signore_Ands]

Thanks for your replies @Anonymous  and @amitchandak  -

This is not quite working for me.

It's probably something silly that I am doing but I can't figure it out.

Here's the calcualtion for my measure:

kg YTD =

VAR CurrentYear = SELECTEDVALUE(calendar_months[calendar_yr])

VAR CurrentMonth = SELECTEDVALUE(calendar_months[month_of_yr])

RETURN

CALCULATE( SUM(Product_Transfers[kg]),

FILTER( ALL(calendar_months),

calendar_months[calendar_yr]=CurrentYear &&

calendar_months[month_of_yr]<=CurrentMonth))

And here are the results:

So it's ignoring the month filter and showing values for each month, but it's not doing the year to date sum.

Any other ideas are more than welcome.  Thanks guys.

[Anonymous]

@Signore_Ands 

This video might be helpful,

https://www.youtube.com/watch?v=aODroF37pq0

Thanks

[amitchandak]

@Signore_Ands , Make sure you have a separate Table with month year, month , year (say date) ,

And try like

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))