Need help to show Year to Date on Table

[Sashwato]

Hello All,

I am struggling to find out ways to show data Year-to-Date on a table with the information available. Below screenshot is my table I have created:

We need to create a YTD selection in such a way - That any user who selects a specific Year, Month say - 2021, May from slicer will get all the table rows from 1/1/2021 till 5/1/2021. As of right now any selection of month will only render that row of the month.

Please let me know if any solution helps. Any help is appreciated!

Regards!

[v-stephen-msft · Accepted solution]

Hi @Sashwato ,

You can create a seperate calendar table.

Calendar = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

And put the hierarchy of year and month into the slicer

The measure is like

Year to date = var _max=MAX('Calendar'[Date])
return CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),[Date]>=DATE(YEAR(_max),1,1)&&[Date]<=_max))

If you still perform classification and summation based on certain columns, you can replace the ALL function with ALLEXCEPT.

The slicer chose April 2021, and the result is 1+2+3+4=10

Best Regards,

Stephen Tao

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

[Preeti_Yadav]

Hi @Sashwato ,

You may try creating the follwoing dax,might help you

Year-To-Date Value = 
    TOTALYTD(SUM('Simple Facts'[Value]),'Calendar'[Date])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Thank You !!

  Preeti

[amitchandak]

@Sashwato , if you select a date and want to show duration more than that, then you need independent date slicer 

YTD Today=

var _1 = maxx(allselected('IDate') , 'IDate'[Date]) // independent date tabel IDate
var _min = date(_1 ,1,1)
var _day = _1
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) ) // Joined date table

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI