cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Prathap1
Frequent Visitor

Need to show week wise cumulative data in clustered

Hi all,

I have a question which I couldn't able to find a solution, please assist me in this.

 

I have an amount data which will grow on a daily basis. I  need to create a clustered column chart which shows the cumulative data

 

Output like below visual

1. Budget amount need to show in all the weeks (Week1, Week2, Week3, Week4..etc)

2. Week amount should be cumulative For example Week1 100$

             Week1 100$

             Week2 200$ - But in chart, it needs to show (Week1+Week2) - where it shows the total amount of growth(300$)

             Week3 300$ - (Week1+Week2+Week3)-(600$)

 

 

Vscreenshot.png

 Data for the above visual: 

 Issuedata.png
Budget amount is for Whole month(5th month of 2017) because in database it will 

DB.png

so the data will be fall under Week1.

 

Issue was mentioned below of output visuals.

 

Regards,

PrathapS

 

 

 

 

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey,

 

in this pbix file there is the report page "YTD variations":

 

as you can see

Cumulative Values.png

 

This is achieved by the measure

Amount YTD Week Of Year (check value exists) = 
//check if there is a value in the "selected" period
//if not, the measure is not calculated
IF(NOT(ISBLANK(CALCULATE(SUM('FactWithDates'[Amount])))), 
	CALCULATE( 
		SUM('FactWithDates'[Amount]),
		FILTER(
			ALL('Calendar'),
			'Calendar'[Date] <= MAX('Calendar'[Date]) &&
			'Calendar'[Year] = MAX('Calendar'[Year])
		)
	)
)

The IF(...) watches over existing values in the used timeslice, in my sample data there is no value in each week, for this reason I'm using the IF() to avoid "clutter" in the table or on the axis of a chart.

Another point you should consider is the power of "CROSS FILTERING", I'm using "just" the weeknumber on the axis, and as we all know, the same weeknumber is valid for more than one year 😉 For this reason I use the additional filter condition checking the year.

Hope this helps

 



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

3 REPLIES 3
TomMartens
Super User
Super User

Hey,

 

in this pbix file there is the report page "YTD variations":

 

as you can see

Cumulative Values.png

 

This is achieved by the measure

Amount YTD Week Of Year (check value exists) = 
//check if there is a value in the "selected" period
//if not, the measure is not calculated
IF(NOT(ISBLANK(CALCULATE(SUM('FactWithDates'[Amount])))), 
	CALCULATE( 
		SUM('FactWithDates'[Amount]),
		FILTER(
			ALL('Calendar'),
			'Calendar'[Date] <= MAX('Calendar'[Date]) &&
			'Calendar'[Year] = MAX('Calendar'[Year])
		)
	)
)

The IF(...) watches over existing values in the used timeslice, in my sample data there is no value in each week, for this reason I'm using the IF() to avoid "clutter" in the table or on the axis of a chart.

Another point you should consider is the power of "CROSS FILTERING", I'm using "just" the weeknumber on the axis, and as we all know, the same weeknumber is valid for more than one year 😉 For this reason I use the additional filter condition checking the year.

Hope this helps

 



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

This worked for me .. Great 

Thank you so much

Glad it works!


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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.