Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Feilin
Helper II
Helper II

Aggregate rows on months with DAX

I'm trying to aggregate a month value as a calculated column, but I'm struggling a bit how I should filter it. I have the following:

 

Month Time = 
CALCULATE(
    SUM(
        Dates[Time to be Aggregated]
    );
    DATESBETWEEN(Dates[Date]; Date(Dates[Year]; Dates[Month]; 1); DATE(Dates[Year]; Dates[Month]; 31)
))

but that doesn't work because not every month has 31 days (also, there has to be something I missed).

 

 

I have also seen various measures with ALL, VALUES, etc, but they always use multiple tables and since I just want to do a "pure date calculation", I end up with Dates[Month] = Dates[Month], which is always true. I don't understand what I can filter against (or how I can adapt the solutions I found to my needs). What I want is for the column to aggregate all the time during the current month for every row, so that all the values for 2018-06-01 through 2018-06-30 should all be the same, for instance, and then another value for other months. When I tried ALL or just Dates[Month] = Dates[Month] it returns the sum of the entire column for every single one...

 

How can I accomplish what I want?

1 ACCEPTED SOLUTION
alexei7
Continued Contributor
Continued Contributor

I'm probably not going to do it justice, but my understanding is that it's like the ALL function, which would remove all filter context (i.e. would sum everything in the table), except it keep the month as the filter context. Therefore, each row is totalled based on what is in the month column.

 

I'm afraid i didnt understand your first question, you'll need to provide more data to show what you mean by these other different columns.

 

Thanks

View solution in original post

6 REPLIES 6
alexei7
Continued Contributor
Continued Contributor

Hi @Feilin,

 

Hopefully I've understood your question, it'd be really helpful if you could provide some data to illustrate what you're trying to do if not.

 

It looks like you already have a date dimension, so can you pull in the "Month" field from your data dimension and then just use a simple sum measure (e.g. "SUM(Dates[Time to be Aggregated])") in the same visual?

 

This should split your metric by month.

 

Hope that helps

Alex

I have a Dates table, which has the following content:

 

Date                  Month   TTBA  (Aggregation)

2018-06-01       6            1         5

2018-06-08       6            4         5

2018-07-08       7            5         12

2018-07-09       7            7         12

2018-08-05       8            3         3

2018-09-15       9            2         2

 

The rightmost column is what I am trying to add.

 

I am not sure what you mean, but that is essentially what I do, but I need to filter it in a good way, so it doesn't sum over the entire column. Also, note, I'm not doing a visual; I'm doing some "date calculations", so I try to make it a measure.

 

alexei7
Continued Contributor
Continued Contributor

Ah ok.

 

Try the following:

 

Aggregation = CALCULATE(sum(Dates[TTBA]),ALLEXCEPT(Dates,Dates[Month]))

 

Hope that helps,

Thanks

Alex 

Almost!

 

I actually have a few different columns (which I didn't mention for simplicity), and what it seems to do is actually what I want, but on all of those columns (i.e. take the aggregate over the period of the month that I want). How do I limit that to only the column selected (which I thought would be automatic...)?

Also, I don't really see how ALLEXCEPT solves the problem of aggregating over a month. Could you explain in a few sentences what it does?

 

Thanks!

alexei7
Continued Contributor
Continued Contributor

I'm probably not going to do it justice, but my understanding is that it's like the ALL function, which would remove all filter context (i.e. would sum everything in the table), except it keep the month as the filter context. Therefore, each row is totalled based on what is in the month column.

 

I'm afraid i didnt understand your first question, you'll need to provide more data to show what you mean by these other different columns.

 

Thanks

Oh, silly me. I just needed to except the Year as well, and it worked (it was the same number of years as additional columns, so I misinterpreted the results).

 

Now it works, thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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