Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am trying to include a column for cumulative sum in Power BI table visual. I have the fields of Financial Year, Month and date available also the sales made per month. Can anyone please help me out on how to include an additional column for a cumulative sum.
My power Bi is extracting data from analysis services (Tabular model)
Thanks,
Akshay
Solved! Go to Solution.
You need a date table to really do this correctly, though it is possible without one. This assumes your date table and field is Date[Date].
[Total Sales] below is simply SUM(Sales[Sales])
Cumulative Total =
VAR CurrentDate = MAX(Sales[Date])
RETURN
CALCULATE(
[Total Sales],
REMOVEFILTERS('Date'[Date]),
'Date'[Date] <= CurrentDate
)
A sample file is at this link.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou need a date table to really do this correctly, though it is possible without one. This assumes your date table and field is Date[Date].
[Total Sales] below is simply SUM(Sales[Sales])
Cumulative Total =
VAR CurrentDate = MAX(Sales[Date])
RETURN
CALCULATE(
[Total Sales],
REMOVEFILTERS('Date'[Date]),
'Date'[Date] <= CurrentDate
)
A sample file is at this link.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingGreat. Glad to help!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou have the option to show running a total of filed. You can use that. Also, you can create a quick measure for that.
Or try a formula like this
till date =
Var _max_date = CALCULATE(maxx(all('Date'[Date Filer]),'Date'[Date Filer]))
Var Sale_till_tody =CALCULATE(sum(Sales[Sales Amount]),filter(sales,Sales[Sales Date]<=maxx(Sales,Sales[Sales Date].[Date])))
Var Sale_till_tody =CALCULATE(sum(Sales[Sales Amount]),Sales[Sales Date]<=_max_date)
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hi Amit,
I applied the following formula in the measure:
@Anonymous Can you provide a quick mock-up with sample data so that we can better visualize what you're working with along with what you'd like the output to be?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |