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])

     

     

     

BFrost888's avatar
BFrost888
Regular Visitor
5 years ago

Let me add the calendar table as well:

 

Date
01-Jan-18
02-Jan-18
03-Jan-18
04-Jan-18
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
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's avatar
    BFrost888
    Regular Visitor
    5 years ago

    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's avatar
      lbendlin
      Icon for Super User rankSuper User
      5 years ago

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