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

[mateus_sjc]

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?

Thanks in advance.

-Matt

[Anonymous]

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

[TomMartens]

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

[mateus_sjc]

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,

[TomMartens · Accepted solution]

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:

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

Regards

[RoRo]

Hello ,maybe the following code will help you :

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

[RoRo]

Hello,the following code may work:

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