Forum Discussion

BFrost888's avatar
BFrost888
Regular Visitor
5 years ago
Solved

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...
Need Help
  • lbendlin's avatar
    lbendlin
    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])

     

     

     

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

Your end date for ID 1 overlaps with the start date for ID 2.  If that is unexpected please adjust your sample data.

 

If the intervals are abutting then you can eliminate the need for a start date column, and your DAX becomes much easier.  If the intervals are overlapping then it can be done but with some more effort and the use of table variables.  You will also want to consider if there could be interval gaps  (in which case the start date column would be required again)

 

Please provide accurate sample data in usable format (not as a picture - maybe insert into a table?).