Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
pchecinsk
Regular Visitor

Value for latest date, changing in time

‎10-29-2024 05:55 AM

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

iddatevalue
121.02.202424400
107.03.202424400
104.04.202424400
102.05.202424400
105.06.202424400
108.07.202430000
107.08.202432000
104.09.202432000
102.10.202432000
221.02.20241500
220.03.20241500
219.04.20241500
221.05.20241500
208.07.20241500
205.08.20243000
205.09.20243000
204.10.20243000
321.02.2024400
328.02.2024400
305.04.2024400
308.05.2024400
306.06.2024590
322.07.2024590
321.08.2024590
323.09.2024590
324.09.2024590
421.02.202423333
404.03.202423333
403.04.202423333
406.05.202423333
403.06.202423333
427.06.202423333
402.07.202423333
410.07.202423333
411.07.202423333
415.07.202423333
424.07.202423333
425.07.202423333
413.08.202423333
414.08.202423333
415.08.202423333
429.08.202423333
402.09.202423333
403.09.202423333
404.09.202423333
411.09.202423333
412.09.202423333
408.10.202423333
521.02.20240
505.03.2024500
502.04.2024500
530.04.2024500
528.05.2024500
513.06.2024500
526.06.20241000
524.07.20241000
521.08.20241000
518.09.20241000
523.09.20241000
621.02.202412000
621.03.202412000
624.04.202412000
606.06.202412000
616.07.202412000
617.07.202415000
627.08.202415000
628.08.202415000
624.09.202415000
721.02.20248000
722.03.20248000
723.04.20248000
720.05.20248000
725.06.20248000
715.07.20249000
712.08.20249000
709.09.20249000
723.09.20249000
708.10.20249000

 

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

 

 

Message 1 of 8
521 Views
0
2 ACCEPTED SOLUTIONS
Anonymous
Not applicable
In response to pchecinsk

‎10-31-2024 01:50 AM

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":

vjialongymsft_0-1730364457963.png


Result:

vjialongymsft_1-1730364494156.png

 

 

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.

 


 

View solution in original post

Value for latest date, changing in time.pbix
Message 6 of 8
369 Views
0
mark_endicott
Super User
Super User
In response to pchecinsk

‎10-31-2024 02:58 AM

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

 

View solution in original post

Message 7 of 8
354 Views
0
7 REPLIES 7
Anonymous
Not applicable

‎10-29-2024 07:27 PM

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:

vjialongymsft_0-1730255199159.png

 

 

 

 

 

 

 

Best Regards,

Jayleny

 

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

Value for latest date, changing in time.pbix
Message 8 of 8
448 Views
0
Greg_Deckler
Community Champion
Community Champion

‎10-29-2024 06:08 AM

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Message 2 of 8
510 Views
0
pchecinsk
Regular Visitor
In response to Greg_Deckler

‎10-30-2024 01:30 AM

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]

Message 3 of 8
431 Views
0
mark_endicott
Super User
Super User
In response to pchecinsk

‎10-30-2024 08:35 AM

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

 

mark_endicott_0-1730302390102.png

 

mark_endicott_1-1730302413323.png

 

mark_endicott_2-1730302444629.png

 

If this works for you, please accept it as the solution, it helps with visibility for others with the same issue.

 

Message 4 of 8
409 Views
0
pchecinsk
Regular Visitor
In response to mark_endicott

‎10-31-2024 12:27 AM

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]

Message 5 of 8
380 Views
0
mark_endicott
Super User
Super User
In response to pchecinsk

‎10-31-2024 02:58 AM

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

 

Message 7 of 8
355 Views
0
Anonymous
Not applicable
In response to pchecinsk

‎10-31-2024 01:50 AM

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":

vjialongymsft_0-1730364457963.png


Result:

vjialongymsft_1-1730364494156.png

 

 

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.

 


 

Value for latest date, changing in time.pbix
Message 6 of 8
370 Views
0

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

View All