Sum the last date in each month

Hi,

I have two tables in my model (Date & Source file). Source file holds daily values. My issue is that year 1 in source file holds a single value per calendar month, however, year 2 holds a single value each day of the calendar month.

So when I compare Q1Y1 vs Q1Y2 my total sum shows £91 vs £2839, and it should be £91 vs £63. This is due to summing the values in each day.

Is there a way of summing only the last day of the month to get the expected values?

Attached sample file
Test.pbix (80.2 KB)

Thanks
Hesham

@Hesham,

There are a number of different ways to do this. Here’s the approach I took:

  1. In Power Query, grouped on month+ year concatenation, and calculated the max date per group

  1. Then in DAX, created the following measure:

     Total Values Last Date Per Month = 
    
    
     CALCULATE(
         SUMX(
             Source,
             Source[Value]
         ),
         FILTER(
             Source,
             Source[Date] = Source[MaxMonthDate]
         )
     )
    

Here’s what it looks like all put together:

I hope this is helpful. Full solution file attached.

2 Likes

Thank you Brian. Simple and easy.

Out of interest, do you have tips for 28/29 Feb time intelligence, please? don’t worry if not.

Hesham