cancel
Showing results for
Did you mean: 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\$) Data for the above visual: Budget amount is for Whole month(5th month of 2017) because in database it will so the data will be fall under Week1.

Issue was mentioned below of output visuals.

Regards,

PrathapS

1 ACCEPTED SOLUTION  Super User

Hey,

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

as you can see 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
3 REPLIES 3  Super User

Hey,

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

as you can see 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 Frequent Visitor

This worked for me .. Great

Thank you so much  Super User

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   