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.
I want to calculate Collection on 1st, 2nd, 3rd, and 4th day of every month on a cumulative basis. And Comparing current month's data with the previous one. An indicative sample of data is given below. Cumulative % is calculated against the Total Invoiced Amount.
I have a Collection Date table, Total Collection Metrics & Cumulative Collection Metrics.
Not able to get the data for the first 4 days of every month.
Collection For June'20 and July'20 | ||||||
Date | June'20 Collection | June'20 Cumulative | June'20 Cumulative % | July'20 Collection | July'20 Cumulative | July'20 Cumulative % |
1st | 10,00000 | 10,00000 | 15% | 11,00000 | 11,00000 | 16% |
2nd | 15,00000 | 25,00000 | 23% | 20,00000 | 31,00000 | 31% |
3rd | 12,00000 | 37,00000 | 30% | 12,00000 | 43,00000 | 40% |
4th | 10,00000 | 47,00000 | 40% | 12,00000 | 55,00000 | 55% |
Hi @AsmitaColive ,
You could refer to this resolved case:
https://community.powerbi.com/t5/Desktop/How-to-do-a-running-Sum-by-group-in-Power-Query/td-p/290123
And you could refer to the following codes in query editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY49DsAgCEbvQuLmwI/Wwxi3Dp06tN4/rZoUKAN54eMRagW6O0QgjDjqhzmMTjowuAVosQKf+9z8AjYow2c9KGRw+XJNnzUoihh8lkQRl5/64Z9Oxe04P2eDr98e", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"June'20 Collection" = _t, #"June'20 Cumulative" = _t, #"June'20 Cumulative %" = _t, #"July'20 Collection" = _t, #"July'20 Cumulative" = _t, #"July'20 Cumulative %" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"June'20 Collection", Int64.Type}, {"June'20 Cumulative", Int64.Type}, {"June'20 Cumulative %", Percentage.Type}, {"July'20 Collection", Int64.Type}, {"July'20 Cumulative", Int64.Type}, {"July'20 Cumulative %", Percentage.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Skip(List.Accumulate(#"Changed Type"[#"June'20 Collection"], {0},(cumulative,cost) => cumulative & {List.Last(cumulative) + cost})){Number.FromText(Text.Start([Date],1))-1})
in
#"Added Custom"
@AsmitaColive - Is that actually how your source data looks? 1st, 2nd, 3rd, 4th in the Date column and not actual dates? If that is the case, use LEFT([Date],2) to extract the first two characters. Get rid of any column ending in a zero. The get the first character LEFT([Date],1) and then you can make this column numeric and filter out anything >4 and then do a running total across this table. Or, use actual dates and skip the misery.
Thanks for the reply. 1st 2nd ...are actual dates...like I want to see this month's 1st to 4th collection and last month's 1to 4th collection...
@AsmitaColive - OK, if they are actual dates, that makes things much easier. You can just do a running/cumulative total by using something like:
SUMX(FILTER('Table',[Date] <= EARLIER('Table'[Date]),[Column])
To be more specific, would have to know how your source data is organized.
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Also, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
I would like to share the sample data.
Have performed a few calculations but not getting the desired result. I think I have to learn more about Date Hierarchy
Here is the sample data link:
https://drive.google.com/file/d/13jcI8tQrNtoeQZGOGbZRYSZrFi2DZqTj/view?usp=sharing
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 |
---|---|
99 | |
50 | |
19 | |
12 | |
11 |