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

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