Forum Discussion
BFrost888
5 years agoRegular Visitor
Referencing a date range to calculate a column value
I am working with a DB table that shows a Value for a given date range, like: I have brought a 2nd table into my model, which is simply a daily calendar: I'm trying to add a 2nd colu...
- 5 years ago
Here is your DateRange table:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIAYgUgNrLQdSxN1zW0VIrViVYyAomgiAJVGuo6FhTpGhmAVRiDZPVMTFEkQEbFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Value = _t, StartDate = _t, EndDate = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type number}, {"StartDate", type date}, {"EndDate", type date}}) in #"Changed Type"Load this into Power BI. Then in DAX create a Calendar table (or use your existing one)
Dates = CALENDAR(DATE(2019,6,1),DATE(2020,6,1))And finally add the calculated column for the factor to the Dates table:
Factor = var a = ADDCOLUMNS(DateRange ,"GTS",if(ISBLANK(DateRange[StartDate]) || [Date]>=DateRange[StartDate],1,0) ,"LTE",if(ISBLANK(DateRange[EndDate]) || [Date]<DateRange[EndDate],1,0)) return SUMX(a,[Value]*[GTS]*[LTE])
BFrost888
5 years agoRegular Visitor
Unfortunately, I can't make changes to the sample data, it comes from a legacy DB in this less-than-ideal structure. But, I've attached the output as a tablet. Thanks for helping.
| ID | Value | StartDate | EndDate |
| 1 | 0 | 28-Aug-19 | |
| 2 | 2 | 28-Aug-19 | 01-Apr-20 |
| 3 | 2.45 | 01-Apr-20 |