Forum Discussion
Trying2Excel
Helper I
1 year agoYTD Measure Not Working
I am trying to calculate YTD with the formula below. It looks like the Total line is just pulling the latest value. Also below are my table connections of one to many on a date table. Please help. ...
- Anonymous1 year ago
Hi Trying2Excel
It looks like the YTD calculations are also incorrect.
Please try this:
MEASURE = VAR _currentYear = YEAR ( MAX ( 'Reductions Table'[Date] ) ) VAR _currentMonth = MONTH ( MAX ( 'Reductions Table'[Date] ) ) RETURN IF ( ISINSCOPE ( Reductions Table[Date].[Year] ), CALCULATE ( SUM ( Reductions Table[Reduction2] ), FILTER ( ALLSELECTED ( Reductions Table ), YEAR ( 'Reductions Table'[Date] ) = _currentYear && MONTH ( 'Reductions Table'[Date] ) <= _currentMonth ) ), CALCULATE ( SUM ( 'Reductions Table'[Reduction2] ), FILTER ( ALLSELECTED ( Reductions Table ), MONTH ( 'Reductions Table'[Date] ) <= _currentMonth ) ) )Hope this can help,
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
1 year agoNot applicable
Hi Trying2Excel
It looks like the YTD calculations are also incorrect.
Please try this:
MEASURE =
VAR _currentYear =
YEAR ( MAX ( 'Reductions Table'[Date] ) )
VAR _currentMonth =
MONTH ( MAX ( 'Reductions Table'[Date] ) )
RETURN
IF (
ISINSCOPE ( Reductions Table[Date].[Year] ),
CALCULATE (
SUM ( Reductions Table[Reduction2] ),
FILTER (
ALLSELECTED ( Reductions Table ),
YEAR ( 'Reductions Table'[Date] ) = _currentYear
&& MONTH ( 'Reductions Table'[Date] ) <= _currentMonth
)
),
CALCULATE (
SUM ( 'Reductions Table'[Reduction2] ),
FILTER (
ALLSELECTED ( Reductions Table ),
MONTH ( 'Reductions Table'[Date] ) <= _currentMonth
)
)
)
Hope this can help,
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Trying2Excel
Helper I
1 year agoThat worked! Thank you so much!