Forum Discussion
Value for latest date, changing in time
Hi Anonymous
Please try the following Dax:Measure = VAR _calendar_month = MONTH(SELECTEDVALUE(tblCalendar[FormattedDate])) RETURN SUMX(FILTER('tblStatus',MONTH('tblStatus'[FormattedDate])=_calendar_month),'tblStatus'[value])
Then click "Show items with no data":
Result:Sample pbix file attached.
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous - this was not made clear in your requirements. This will populate the prevoius months value where there is not a value for an id.
VAR _end_month = MAX ( 'tblCalendar'[date] ) VAR _start_month = EOMONTH ( _end_month, -1 ) + 1 VAR _val = SUMX ( FILTER ( 'tblStatus', 'tblStatus'[date] >= _start_month && 'tblStatus'[date] <= _end_month ), 'tblStatus'[value] ) VAR _prev_month_end = EOMONTH ( _end_month, -1 ) VAR _prev_month_start = EOMONTH ( _end_month, -2 ) + 1 VAR _Prev_val = SUMX ( FILTER ( 'tblStatus', 'tblStatus'[date] >= _prev_month_start && 'tblStatus'[date] <= _prev_month_end ), 'tblStatus'[value] ) RETURN IF ( NOT ISBLANK ( _val ), _val, _Prev_val )If this works for you, please accept it as the solution, it helps with visibility for others with the same issue.
Hi Greg, thanks for your quick response! In general, I find your solution helpful, thanks for insight!
However, the formula returns [value] only for max [id] - I need to have sum for all [id] of [value] that are relevant for given [Calendar date]
Super UserAnonymous - This version will show a total for all id's and is filterable by ID. It will show you the value for the whole month at the end of the month.
VAR _end_month =
MAX ( 'tblCalendar'[date] )
VAR _start_month =
EOMONTH ( _end_month, -1 ) + 1
RETURN
SUMX (
FILTER (
'tblStatus',
'tblStatus'[date] >= _start_month
&& 'tblStatus'[date] <= _end_month
),
'tblStatus'[value]
)
Below are some screenshots to show unfiltered, and then filtered by ID.
If this works for you, please accept it as the solution, it helps with visibility for others with the same issue.
Hi Mark, thanks for your idea. Yet, still it's not fulfilling my needs... In my dataset there can be situation when some [id]s don't have [value] in some months, but still should be added to calculation. For every [id], I need to find the most recent [value] depending on [date]
Hi Anonymous
Please try the following Dax:Measure = VAR _calendar_month = MONTH(SELECTEDVALUE(tblCalendar[FormattedDate])) RETURN SUMX(FILTER('tblStatus',MONTH('tblStatus'[FormattedDate])=_calendar_month),'tblStatus'[value])
Then click "Show items with no data":
Result:Sample pbix file attached.
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- mark_endicott
Anonymous - this was not made clear in your requirements. This will populate the prevoius months value where there is not a value for an id.
VAR _end_month = MAX ( 'tblCalendar'[date] ) VAR _start_month = EOMONTH ( _end_month, -1 ) + 1 VAR _val = SUMX ( FILTER ( 'tblStatus', 'tblStatus'[date] >= _start_month && 'tblStatus'[date] <= _end_month ), 'tblStatus'[value] ) VAR _prev_month_end = EOMONTH ( _end_month, -1 ) VAR _prev_month_start = EOMONTH ( _end_month, -2 ) + 1 VAR _Prev_val = SUMX ( FILTER ( 'tblStatus', 'tblStatus'[date] >= _prev_month_start && 'tblStatus'[date] <= _prev_month_end ), 'tblStatus'[value] ) RETURN IF ( NOT ISBLANK ( _val ), _val, _Prev_val )If this works for you, please accept it as the solution, it helps with visibility for others with the same issue.