Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am looking to fill in blank values with the most recent values that resets to 0 after the new year. This worked last year because the data only had 2023 in it. Now the data goes until the end of 2024... You can see that in 2023 the YTD Count works correctly, taking the values found and missing in the Lookup row and filling them in. All looks well in this example because there just so happens to be a value for YearMonth 2024-1 in Lookup (2). In the case where that value is subsequently 0 my dilemna arises. it will pull the most recent value from 2023-9 of 15. I want that to reset at 2024-1 to be 0.
The formula i have so far (works for a single year but doesnt work in the case of more than 1 year):
Solved! Go to Solution.
@Anonymous
Use this calculated column:
YTD Count =
VAR _whspart = 'Whs Part Month'[Whs and Part]
VAR _date = 'Whs Part Month'[Date]
VAR _year = YEAR('Whs Part Month'[Date])
VAR _T1 =
FILTER(
'Whs Part Month',
'Whs Part Month'[Whs and Part] = _whspart &&
'Whs Part Month'[Date] <= _date &&
'Whs Part Month'[Lookup] <> BLANK() &&
YEAR( 'Whs Part Month'[Date] ) = _year
)
VAR _DateLast = MAXX( _T1 , 'Whs Part Month'[Date] )
VAR _result = MAXX( FILTER( _T1 , 'Whs Part Month'[Date] = _DateLast ) , 'Whs Part Month'[Lookup] )
RETURN
COALESCE(_result,0)
Scenario with no value to start 2024.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Use this calculated column:
YTD Count =
VAR _whspart = 'Whs Part Month'[Whs and Part]
VAR _date = 'Whs Part Month'[Date]
VAR _year = YEAR('Whs Part Month'[Date])
VAR _T1 =
FILTER(
'Whs Part Month',
'Whs Part Month'[Whs and Part] = _whspart &&
'Whs Part Month'[Date] <= _date &&
'Whs Part Month'[Lookup] <> BLANK() &&
YEAR( 'Whs Part Month'[Date] ) = _year
)
VAR _DateLast = MAXX( _T1 , 'Whs Part Month'[Date] )
VAR _result = MAXX( FILTER( _T1 , 'Whs Part Month'[Date] = _DateLast ) , 'Whs Part Month'[Lookup] )
RETURN
COALESCE(_result,0)
Scenario with no value to start 2024.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group