cancel
Showing results for
Did you mean:
Frequent Visitor

## Cumulative sum group by day of month - with columns of months

Hey, guys

Could you please help me (kind of a begginer here). I want to make a matrix with cumulative daily sales grouped by day of month and organized in monthly columns

I will try to explain, imagine this:

• January 1st we sold: \$15
• January 2nd we sold: \$20
• ...

So, at day 2 of January we would have \$35 (which is day 1 plus day 2 of january).

• February 1st we sold: \$8
• February 2nd we sold: \$4

So, at day 2 of February we would have \$12(which is day 1 plus day 2 of february).

I'm trying to make a matrix like this:

Currently the best code so far was:

```Cumulative = CALCULATE (
SUM ( 'Daily - by Índices'[Daily Revenue] ),
FILTER (
ALLSELECTED('Daily - by Índices'[Data]),
'Daily - by Índices'[Data] <= MAX ('Daily - by Índices'[Data] )
)
)```

However, I was only able to create such a matrix with rows like "DATE" without hierarchy (each day appears in a single row in a endless table)

I would like to reinforce that apparently the code above WORKS, however I want to group rows by day of month (not have a single date in each row).

• When I tried to select DATE by DAY (using hierarchy) my metric (Daily Revenue) stoped to be cumulative

Any ideas?

-Matt

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Cumulative sum group by day of month - with columns of months

Hey,

I created a new measure

```Amount Month ToDate =
CALCULATE(
SUM('FactWithDates'[Amount])
,FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <= MAX('Calendar'[Date])
&& YEAR('Calendar'[Date]) = YEAR(MAX('Calendar'[Date]))
&& MONTH('Calendar'[Date]) = MONTH(MAX('Calendar'[Date]))
)
)```

I guess this measure creates what your are looking for:

Be aware that I also checked "Show items with no data" for the columns and rows.

Regards

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
6 REPLIES 6
Super User

## Re: Cumulative sum group by day of month - with columns of months

Hey,

maybe this is what you are looking for, here is PBI file, have a look at the page "Matrix Visual":

This is the Measure "Amount ToDate" I'm using in the Matrix

```Amount ToDate =
IF(NOT(ISBLANK(SUM('FactWithDates'[Amount])))
,CALCULATE(
SUM('FactWithDates'[Amount])
,FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <= MAX('Calendar'[Date])
&& YEAR('Calendar'[Date]) = YEAR(MAX('Calendar'[Date])))
)
)```

Maybe this helps

Regards

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Frequent Visitor

## Re: Cumulative sum group by day of month - with columns of months

Hm... no, sorry

I don't wanna cross information between months. I'm trying to reproduce the exaclty same table that I posted

Let me try this: "I wish to be able to compare results between months, so I need cumulative results from each month."

Rows with days of months and each month with your on information and cumulative.

Imagine the following results of 4 sales during the period:

What I expect from these results is exactly a matrix like this:

• EACH MONTH with their OWN specific results, they're just organized in the same row by the day of the month. However, results from February are from February and from January from January.
• Also, note that only 4 sales were made, but we have the table fulfilled once it's a cumulative result.

So:

• on January 1st one sale was made: \$10
• on january 5th the last one: \$18

Note that from day 1 - 4 January we only have \$10, which is from that one sale.

Then, from day 5-31 we have \$28 repeateadly (which is \$10 from first sale + \$18 from the last)

Also:

• February results have nothing to do with January
• No sale was made until February 9th, when we add \$15
• Then, only on February 13th we have another \$6 from a sale
• resulting in a \$21 result

The code that I posted works, actually. However, only with DATE, not DATE HIERARCHY. So, the result is a long table with each date in a different row - which sucks badly to compare results.

I wish to be able to compare results between months, so I need cumulative results from each month.

I think now I'm better expressing myself

Regards,

Frequent Visitor

## Re: Cumulative sum group by day of month - with columns of months

Hello,the following code may work:

```Cumulative = CALCULATE (
SUM ( 'Daily - by Índices'[Daily Revenue] ),
DATESMTD( 'Daily - by Índices'[Data] )
)```

Frequent Visitor

## Re: Cumulative sum group by day of month - with columns of months

```Cumulative = CALCULATE (
SUM ( 'Daily - by Índices'[Daily Revenue] ),
DATESMTD( 'Daily - by Índices'[Data] )
)```

Super User

## Re: Cumulative sum group by day of month - with columns of months

Hey,

I created a new measure

```Amount Month ToDate =
CALCULATE(
SUM('FactWithDates'[Amount])
,FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <= MAX('Calendar'[Date])
&& YEAR('Calendar'[Date]) = YEAR(MAX('Calendar'[Date]))
&& MONTH('Calendar'[Date]) = MONTH(MAX('Calendar'[Date]))
)
)```

I guess this measure creates what your are looking for:

Be aware that I also checked "Show items with no data" for the columns and rows.

Regards

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Community Support Team

## Re: Cumulative sum group by day of month - with columns of months

Hi @mateus_sjc,

For your requirement, you want to get month to date total, right?
If this is a case, you can create a measure to sum of sale by filter on year,month and date.

After above steps, creata a matrix visual, switch date to hierarchy mode, day part to row, month to column filed, drag above measure to value field.

```Result =
var current_date=MAX('Fact'[Date])
return
SUMX(FILTER(ALLSELECTED('Fact'),[Date].[Year]=YEAR(current_date)&&[Date].[MonthNo]=MONTH(current_date)&&[Date]<=current_date),[Amount])```

Compare:

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |