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

 

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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

6 REPLIES 6
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.
TomMartens
Super User
Super User

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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 🙂

 

Regards,

 

 

 

 

 

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hello ,maybe the following code will help you :

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

 

Hello,the following code may work:

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

 

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.