Measure to find the value by latest date for a month

[moumitadeb]

Hello Team,

I have a DAX table as below: (this data is calculated from a Base Table)

DATE_OF_EXTRACTION	SUM	START_OF_MONTH
8/5/2024 0:00	3991.233	8/1/2024 0:00
8/12/2024 0:00	3936.906	8/1/2024 0:00
8/26/2024 0:00	3905.222	8/1/2024 0:00
9/2/2024 0:00	3900.821	9/1/2024 0:00
9/9/2024 0:00	3911.058	9/1/2024 0:00
9/16/2024 0:00	3895.96	9/1/2024 0:00
9/30/2024 0:00	3898.85	9/1/2024 0:00
10/7/2024 0:00	3857.748	10/1/2024 0:00
10/14/2024 0:00	3872.91	10/1/2024 0:00
10/21/2024 0:00	3884.374	10/1/2024 0:00
10/28/2024 0:00	3911.022	10/1/2024 0:00

I have to create a measure to find the below output (find the last SUM value for each month) :

SUM

3905.222

3898.85

3911.022

Any help is appreciated !

[v-zhengdxu-msft]

Hi @moumitadeb 

Please try this measure:

MEASURE =
VAR _currentMonth =
    MONTH ( MAX ( 'Table'[DATE_OF_EXTRACTION] ) )
VAR _maxDate =
    CALCULATE (
        MAX ( 'Table'[DATE_OF_EXTRACTION] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            MONTH ( 'Table'[DATE_OF_EXTRACTION] ) = _currentMonth
        )
    )
RETURN
    IF ( MAX ( 'Table'[DATE_OF_EXTRACTION] ) = _maxDate, SUM ( 'Table'[SUM] ) )

The result is as follow:

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.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[FreemanZ]

hi @moumitadeb 

try like:

measure =

VAR _start = MAX(data[START_OF_MON])

RETURN

MAXX(

    TOPN(

        1,

        FILTER(

           data,

           EOMONTH(data[DATE_OF_EXTRACTION], -1) + 1= _start

       ),

       data[DATE_OF_EXTRACTION]

    ),

   data[SUM]

)

[PhilipTreacy · Accepted solution]

@moumitadeb 

Download PBIX file with example below

Create a column in your table to indicate the last day in each month with an entry

LastDayInMonth = 

VAR _LastDate = CALCULATE(MAX('DataTable'[Date_of_Extraction]), ALLEXCEPT('DataTable', 'DataTable'[Start_of_Month]))

RETURN

    IF('DataTable'[Date_of_Extraction] = _LastDate, TRUE(), FALSE())

In a Table, add the Date_of_Extraction and Sum.  Add a filter using LastDayInMonth and set that to TRUE.

Regards

Phil

[moumitadeb]

Thanks for your help. It worked .

Is this possible to achieve LastDayInMonth in power query and set a flag for TRUE/FALSE . I have a different scenario to achieve with same condition in Power Query.

Please guide !

[Bibiano_Geraldo]

Hi @moumitadeb ,

First, create a calculated column in your table to identify the last day for each month using this DAX:

IsLatestDayForMonth = 
VAR LastDateInMonth = 
    CALCULATE(
        MAX('YourTable'[DATE_OF_EXTRACTION]), 
        ALLEXCEPT('YourTable', 'YourTable'[START_OF_MONTH])
    )
RETURN 
    IF('YourTable'[DATE_OF_EXTRACTION] = LastDateInMonth, TRUE(), FALSE())

And then, create a measure using this DAX:

LastSUMForMonth = 
CALCULATE(
    SUM('YourTable'[SUM]),
    FILTER(
        'YourTable',
        'YourTable'[IsLatestDayForMonth] = TRUE()
    )
)

By the sample data you gave, the final result was 11 715,094:

I hope this help you, if yes, please give a kudo and mark the reply as solution