topic Value for latest date, changing in time in DAX Commands and Tips

Value for latest date, changing in time

pchecinsk — Tue, 29 Oct 2024 12:57:30 GMT

Hi,

I have a problem with creating correct DAX formula that will sum [value] for latest [date], for each [id]. BUT I also have a classic date table, and want to show the sum related to different dates.

Something like snapshot of [value] for latest [date], but in different moments of time, for example as of end of month.

Sample input data below:

tblStatus

id	date	value
1	21.02.2024	24400
1	07.03.2024	24400
1	04.04.2024	24400
1	02.05.2024	24400
1	05.06.2024	24400
1	08.07.2024	30000
1	07.08.2024	32000
1	04.09.2024	32000
1	02.10.2024	32000
2	21.02.2024	1500
2	20.03.2024	1500
2	19.04.2024	1500
2	21.05.2024	1500
2	08.07.2024	1500
2	05.08.2024	3000
2	05.09.2024	3000
2	04.10.2024	3000
3	21.02.2024	400
3	28.02.2024	400
3	05.04.2024	400
3	08.05.2024	400
3	06.06.2024	590
3	22.07.2024	590
3	21.08.2024	590
3	23.09.2024	590
3	24.09.2024	590
4	21.02.2024	23333
4	04.03.2024	23333
4	03.04.2024	23333
4	06.05.2024	23333
4	03.06.2024	23333
4	27.06.2024	23333
4	02.07.2024	23333
4	10.07.2024	23333
4	11.07.2024	23333
4	15.07.2024	23333
4	24.07.2024	23333
4	25.07.2024	23333
4	13.08.2024	23333
4	14.08.2024	23333
4	15.08.2024	23333
4	29.08.2024	23333
4	02.09.2024	23333
4	03.09.2024	23333
4	04.09.2024	23333
4	11.09.2024	23333
4	12.09.2024	23333
4	08.10.2024	23333
5	21.02.2024	0
5	05.03.2024	500
5	02.04.2024	500
5	30.04.2024	500
5	28.05.2024	500
5	13.06.2024	500
5	26.06.2024	1000
5	24.07.2024	1000
5	21.08.2024	1000
5	18.09.2024	1000
5	23.09.2024	1000
6	21.02.2024	12000
6	21.03.2024	12000
6	24.04.2024	12000
6	06.06.2024	12000
6	16.07.2024	12000
6	17.07.2024	15000
6	27.08.2024	15000
6	28.08.2024	15000
6	24.09.2024	15000
7	21.02.2024	8000
7	22.03.2024	8000
7	23.04.2024	8000
7	20.05.2024	8000
7	25.06.2024	8000
7	15.07.2024	9000
7	12.08.2024	9000
7	09.09.2024	9000
7	23.09.2024	9000
7	08.10.2024	9000

tblCalendar

date
31.01.2024
29.02.2024
31.03.2024
30.04.2024
31.05.2024
30.06.2024
31.07.2024
31.08.2024
30.09.2024
31.10.2024
30.11.2024
31.12.2024

Required output (pivot table):

e.g. for [id]=1:

date (from tblCalendar)	value (from tblStatus)
31.05.2024 (end of May)	24400
30.06.2024 (end of June)	24400
31.07.2024 (end of July)	30000
31.08.2024 (end of August)	32000

Re: Value for latest date, changing in time

Greg_Deckler — Tue, 29 Oct 2024 13:08:23 GMT

pchecinsk Try:

Measure = VAR __ID = MAX( 'tblStatus'[id] ) VAR __Date = MAX( 'tblCalendar'[date] ) VAR __Table = FILTER( 'tblStatus', [id] = __ID && [date] <= __Date ) VAR __MaxDate = MAXX( __Table, [date] ) VAR __Result = MAXX( FILTER( __Table, [date] = __MaxDate ), [value] ) RETURN __Result

Re: Value for latest date, changing in time

Anonymous — Wed, 30 Oct 2024 02:27:20 GMT

Thank you for your prompt reply Greg_Deckler !

Hi pchecinsk

First, you need to change the dates in your two tables to a date form that can be recognized.

FormattedDate = VAR _ConvertedDate = DATE( VALUE(RIGHT([date], 4)), VALUE(MID([date], 4, 2)), VALUE(LEFT([date], 2)) ) RETURN FORMAT(_ConvertedDate, "MM/DD/YYYY")

Then try the following measure:

Measure = VAR _calendar_month = MONTH(SELECTEDVALUE(tblCalendar[FormattedDate])) VAR _select_id = SELECTEDVALUE(tblStatus[id]) RETURN SUMX(FILTER('tblStatus',MONTH('tblStatus'[FormattedDate])=_calendar_month && 'tblStatus'[id] = _select_id),'tblStatus'[value])

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Value for latest date, changing in time

pchecinsk — Wed, 30 Oct 2024 08:30:12 GMT

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]

Re: Value for latest date, changing in time

mark_endicott — Wed, 30 Oct 2024 15:35:05 GMT

pchecinsk - 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.

Re: Value for latest date, changing in time

pchecinsk — Thu, 31 Oct 2024 07:27:26 GMT

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]

Re: Value for latest date, changing in time

Anonymous — Thu, 31 Oct 2024 08:50:01 GMT

Hi pchecinsk

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.

Re: Value for latest date, changing in time

mark_endicott — Thu, 31 Oct 2024 09:58:43 GMT

pchecinsk - 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.