Forum Discussion
Helper IIYTD 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 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
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
4 Replies
iW1DOVI uploaded a sample report file here: Sample report file
Hope the link works...
Gianfranco- divyed
Super User
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?
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
