Sum the last date in each month

[Hesham]

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

[BrianJ]

@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

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

• Brian
  eDNA Forum - Sum Max Day per Month Solution.pbix (104.9 KB)

[Hesham]

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