cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mateus_sjc Frequent Visitor
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 Smiley Happy

 

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:

 

cumulative.PNG

 

 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)

 

cumulative data.PNG

 

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?

 

Thanks in advance.

-Matt

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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]))
		)
)

and updated the ReportPage in the file I mentioned above, redownload please.

 

I guess this measure creates what your are looking for:

Month To Date Calculations.png

 

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
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":

Matrix Visual.png

 

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!
mateus_sjc Frequent Visitor
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 Smiley Sad

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:

 

Data - to cumula.JPG

 

 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

 

data - cumulative.JPG

 

 

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 Smiley Happy

 

Regards,

 

 

 

 

 

 

RoRo Frequent Visitor
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] )
)

 

RoRo Frequent Visitor
Frequent Visitor

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

Hello ,maybe the following code will help you :

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

 

Super User
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]))
		)
)

and updated the ReportPage in the file I mentioned above, redownload please.

 

I guess this measure creates what your are looking for:

Month To Date Calculations.png

 

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
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])

1.PNG

 

Compare:

2.PNG

 

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: | |