Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
This worked for me .. Great
Thank you so much
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |