Referencing a date range to calculate a column value

[BFrost888]

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 column to the Date table that selects the appropriate Value from the first. The logic (in English) would be:

If the Date is less than August 26, 2019 (row 1 end date), the Value is "0"

If the Date is between August 26, 2019 (row 2 start date) and April 1, 2020 (row 2 end date), the Value is "2"

If the Date is greater than April 1, 2020 (row 3 start date), the Value is "2.45"

But, I have been unable to find a DAX function that works.  I've tried using LOOKUPVALUE, but it seems to want an exact number and not a range.

Any suggestions?  Thank you. 

[lbendlin]

Here's a cuter but less readable version:

Factor =
SUMX (
    ADDCOLUMNS (
        DateRange,
        "GTS", [Date] >= COALESCE ( DateRange[StartDate], [Date] ),
        "LTE", [Date]  < COALESCE ( DateRange[EndDate], [Date] + 1 )
    ),
    [Value] * [GTS] * [LTE]
)

[BFrost888]

Let me add the calendar table as well:

Date

01-Jan-18

02-Jan-18

03-Jan-18

04-Jan-18

[lbendlin · Accepted solution]

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]

This is brilliant.  I'm just a bit confused about the first section ("let source =...") - how is this loaded into PowerBI?  

Either way, the Calendar function is awesome and the Calculated Column works perfectly - thanks so much!

[lbendlin]

The first section is done in Power Query.  Create a blank query, open its Advanced Editor, and then replace the code.

[BFrost888]

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

[lbendlin]

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?).