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.
See example below. I am trying to consolidate the date ranges depending if they are continuous or not and then aggregate it. Any help is appreciated!
Problem | ID | First | Last | First DOS | Last DOS | Paid |
1 | John | Smith | 1/1/2017 | 1/15/2017 | $100.00 | |
2 | John | Smith | 1/16/2017 | 1/20/2017 | $100.00 | |
3 | John | Smith | 1/21/2017 | 1/22/2017 | $100.00 | |
4 | John | Smith | 1/25/2017 | 1/27/2017 | $50.00 | |
Desired Result | First | Last | First DOS | Last DOS | Paid | |
John | Smith | 1/1/2017 | 1/22/2017 | $300.00 | ||
John | Smith | 1/25/2017 | 1/27/2017 | $50.00 |
First, very nicely done with presenting the full picture of your data in a form that can be easily copy and pasted as well as the desired result. Seems like you could potentially use the technique described here...
See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
...to retun the MAX date of "First DOS" that is less than the "First DOS" of the current row (EARLIER) and then check if the difference between "Last DOS" of the current row minus that is 1. That would indicate that it is part of a continuous series. Obviously you would also have constraints for First and Last. Once you had that, you could potentially create a measure that did want you wanted but I haven't thought it that far through. If I have some time tonight I will look into this a little more.
OK, I think I have this. First, in your query add an Index that starts at 1. This is my query (Enter Data).
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLKz8gDUsG5mSUZQNpQ31DfyMDQHMI0hbFVDA0M9AwMFJRidaKVjLBqM0PoMzLAqs8Ymz4jJPuMjLDqM8GqzxRJnzlcnylUWywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, First = _t, Last = _t, #"First DOS" = _t, #"Last DOS" = _t, Paid = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"First", type text}, {"Last", type text}, {"First DOS", type date}, {"Last DOS", type date}, {"Paid", Currency.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "ID", "First", "Last", "First DOS", "Last DOS", "Paid"}) in #"Reordered Columns"
My table created by this query is called "Problems". OK, now create the column as above, here is the code again:
Column = VAR mydate = MAXX(FILTER(ALL(Problems),Problems[First DOS]<EARLIER(Problems[First DOS]) && Problems[First]=EARLIER(Problems[First]) && Problems[Last]=EARLIER(Problems[Last])),Problems[Last DOS]) RETURN IF(Problems[First DOS]-mydate = 1,1,0)
Now create this second column like this:
Column 2 = IF([Column]=0,[Index], VAR myindex = MAXX(FILTER(ALL(Problems),Problems[Last DOS]<EARLIER(Problems[First DOS]) && Problems[First]=EARLIER(Problems[First]) && Problems[Last]=EARLIER(Problems[Last]) && [Column]=0),Problems[Index]) RETURN myindex)
You should end up with a table like this:
ID | First | Last | First DOS | Last DOS | Paid | Column | Index | Column 2 |
1 | John | Smith | Sunday, January 1, 2017 | Sunday, January 15, 2017 | $100 | 0 | 1 | 1 |
2 | John | Smith | Monday, January 16, 2017 | Friday, January 20, 2017 | $100 | 1 | 2 | 1 |
3 | John | Smith | Saturday, January 21, 2017 | Sunday, January 22, 2017 | $100 | 1 | 3 | 1 |
4 | John | Smith | Wednesday, January 25, 2017 | Friday, January 27, 2017 | $50 | 0 | 4 | 4 |
Now create a Table visualization in the Report pane and place First, Last, Earliest First DOS, Latest Last DOS, Column 2 and Paid and you should get this:
Probably a more elegant way but this is the first thing I thought of. I was trying to account for the possibility that the Index may not be contiguous between rows in a sequence but I may not have thought of every possible boundary case.
Here is the formula which I was speaking about:
Column = VAR mydate = MAXX(FILTER(ALL(Problems),Problems[First DOS]<EARLIER(Problems[First DOS]) && Problems[First]=EARLIER(Problems[First]) && Problems[Last]=EARLIER(Problems[Last])),Problems[Last DOS]) RETURN IF(Problems[First DOS]-mydate = 1,1,0)
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |