Obtain sum value of last date of the month

[fksng66]

I'm trying to work out the formula of grabing the sum value of the last date of the latest month, while it did managed to grab the last date but the sum value doesn't seem to tally with the actual number of audience from my sheet. 

Expected result: 132,240

End result: 132,163

Did i missed out anything? i couldn't figure out where went wrong.

LastDateSumAudience = 
VAR LastDateOfMonth = 
    CALCULATE(
        MAX('Follower'[Date]), //finds the last date in each month
        ALLEXCEPT('Follower', 'Follower'[Month]) //ensures that the calculation happens within each month
    )
RETURN
CALCULATE(
    SUM('Follower'[Audience]), 'Follower'[Date] = LastDateOfMonth 
    //sums only the rows where the date matches the last date of that month
)

Date	Network	Audience
31/1/2025	X	68
31/1/2025	X	83
31/1/2025	X	103
31/1/2025	Facebook	28,869
31/1/2025	Facebook	5,814
31/1/2025	Facebook	57,545
31/1/2025	Facebook	288
31/1/2025	Instagram	1,896
31/1/2025	Instagram	1,434
31/1/2025	Instagram	252
31/1/2025	Instagram	246
31/1/2025	LinkedIn	12,177
31/1/2025	LinkedIn	1,224
31/1/2025	LinkedIn	9,659
31/1/2025	LinkedIn	110
31/1/2025	YouTube	11,500
31/1/2025	YouTube	972

[fksng66 · Accepted solution]

UPDATES: finally found the issue, it was due to the date format that it wasn't able to grab all the correct value. i removed the original table from power bi and reimport back, change data type on the date column to text, then Change Type > Using Locale > change to your local date format, and it finally able to grab the expected result: 132,240 

[Rupak_bi]

Hi @fksng66

I dont see a month column in the sample data. So I added and the measure works perfect. 

[DataNinja777]

Hi @fksng66 ,

Your DAX measure is structured well, but the discrepancy likely arises from how LastDateOfMonth is determined. The use of ALLEXCEPT('Follower', 'Follower'[Month]) might not be filtering the latest month's last date correctly when applied in the final CALCULATE. Instead of relying on ALLEXCEPT, try ensuring that you are always filtering for the latest month and summing values only for that last date.

LastDateSumAudience =
VAR LatestMonth =
    MAXX(ALL('Follower'), 'Follower'[Month]) // Finds the latest month in the dataset
VAR LastDateOfLatestMonth =
    MAXX(
        FILTER(ALL('Follower'), 'Follower'[Month] = LatestMonth),
        'Follower'[Date]
    ) // Finds the last date in the latest month
RETURN
CALCULATE(
    SUM('Follower'[Audience]),
    'Follower'[Date] = LastDateOfLatestMonth
)

This approach ensures that MAXX(ALL('Follower'), 'Follower'[Month]) correctly identifies the latest month, then MAXX(FILTER(ALL('Follower'), 'Follower'[Month] = LatestMonth), 'Follower'[Date]) finds the last date within that month, and finally, the SUM('Follower'[Audience]) is filtered for that specific last date.
If the discrepancy persists, consider using SUMX for row-level evaluation to confirm that all expected values are being summed:

LastDateSumAudience_SUMX =
VAR LatestMonth =
    MAXX(ALL('Follower'), 'Follower'[Month]) // Ensures the calculation is independent of filters
VAR LastDateOfLatestMonth =
    MAXX(
        FILTER(ALL('Follower'), 'Follower'[Month] = LatestMonth),
        'Follower'[Date]
    ) // Finds the last date within the latest month
RETURN
SUMX(
    FILTER(ALL('Follower'), 'Follower'[Date] = LastDateOfLatestMonth),
    'Follower'[Audience]
)

This version explicitly iterates over the filtered rows using SUMX, ensuring that only rows corresponding to the last date are included in the sum. By applying ALL('Follower'), the calculation remains independent of any existing filters in the visual, reducing the chances of missing values. If the numbers still do not match, verify the dataset for duplicate values, unexpected filters, or missing data.

Best regards,

[fksng66]

i've tried both of your methods including the others who've posted here but unfortunately it still gives the same 132,163 instead. 

tho i've tried to add a new row with a big figure to see if it picked up but it didn't, by right i should be getting 232,240 with the new row, but the number still reminded same.

Date	Network	Audience
31/1/2025	X	68
31/1/2025	x	100000
31/1/2025	X	83
31/1/2025	X	103
31/1/2025	Facebook	28,869
31/1/2025	Facebook	5,814
31/1/2025	Facebook	57,545
31/1/2025	Facebook	288
31/1/2025	Instagram	1,896
31/1/2025	Instagram	1,434
31/1/2025	Instagram	252
31/1/2025	Instagram	246
31/1/2025	LinkedIn	12,177
31/1/2025	LinkedIn	1,224
31/1/2025	LinkedIn	9,659
31/1/2025	LinkedIn	110
31/1/2025	YouTube	11,500
31/1/2025	YouTube	972

[fksng66]

@Rupak_bi @Bibiano_Geraldo i did check the filter panel and the filter was cleared, no filter applied. however, when i tried to tick to filter only show 31/1/2025 on the date column on the query editor it was able to display the correct number. now im even more confused what went wrong.

[Rupak_bi]

See,

What I feel, your "allexcept" function may creating the issue. If you are already using month as slicer, this "allexcept" is not required. Try that once.

VAR LastDateOfMonth = 
    CALCULATE(
        MAX('Follower'[Date])) //finds the last date in each month
        
    

[fksng66]

didn't work. still the same tho.

[Rupak_bi]

Please share some more sample data exactly representing the actual table. if the actual table have multiple year data, include that. currently the sample data you shared have only single date entries. 

[fksng66]

here i've included the data from 1st jan, hope it's enough for you to work on.

https://drive.google.com/file/d/151jDLK88b1cTfY0WApRBHptPzmd6bmg0/view?usp=sharing 

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[Rupak_bi]

then check your filter pane if there is any underlying filter. 

[Bibiano_Geraldo]

Maybe you have some filters in your panel excluding some values, please double check your filters.

[bhanu_gautam]

@fksng66 , Try using

DAX
LastDateSumAudience =
VAR LastDateOfMonth =
CALCULATE(
LASTDATE('Follower'[Date])
)
RETURN
CALCULATE(
SUM('Follower'[Audience]),
'Follower'[Date] = LastDateOfMonth
)

[Bibiano_Geraldo]

HI @fksng66 ,

Please try this:

LastDateSumAudience =
VAR LastDateOfMonth =
    CALCULATE(
        MAX('Follower'[Date]),
        ALLEXCEPT('Follower', 'Follower'[Month])
    )
RETURN
CALCULATE(
    SUM('Follower'[Audience]),
    FILTER(
        ALL('Follower'),
        INT('Follower'[Date]) = INT(LastDateOfMonth)
    )
)