Forum Discussion
YTD value until selected month not working
- Anonymous1 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
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