Forum Discussion

iW1DOV's avatar
iW1DOV
Icon for Helper II rankHelper II
1 year ago
Solved

YTD value until selected month not working

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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's avatar
divyed
Icon for Super User rankSuper User
1 year ago

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's avatar
iW1DOV
Icon for Helper II rankHelper II
1 year ago

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?