DAX obtaining time series from Start and End Date

Question

I have a series of events with a Start and End date, with a Value and a series of other attributes.

 Country --- Location --- Start-Date --- End-Date  ---  Value per day
 Italy       Rome         2018-01-01     2018-03-15     50
 Belgium     BXL          2017-12-04     2017-12-6      120
 Italy       Milan        2018-03-17     2018-04-12     80

I want to convert this, in DAX, to a monthly time-series like:

Country  --- Location --- Month ---  Value per day
Italy       Rome         2018-01      50
Italy       Rome         2018-02      50
Italy       Rome         2018-03      22.58 (= 50 /31*(31-17) days)

The value is a weighted average of industrial capacity. I have done this with a CROSS JOIN with the Calendar table, but this is quite heavy and requires to calculate each possible value, while a calculation on-the-fly would be likely faster.

Any help? Many thanks

What is the logic, i.e. how 2018-01-01 to 2018-03-15 result in 31*15 days? — Prakash Gautam
@PrakashGautam - EDITED for clearer dates. The logic is the overlap of the period with the month. If its starts 17th March and ends in April, the number of days for march are 31-17 = 14, so 14/31 /31 = 45%, times the Value per day = 22.58 — vale_p

Prakash Gautam Prakash Gautam · Accepted Answer · 2018-07-07T08:07:08

DAX would be similar to:

Total = 
Var DayDiff =
            SUMMARIZE(Table1,Appointments[End-Date],
            "DayDiff",DATEDIFF(min(Table1[Start-Date]),MAX(Table1[End-Date]),DAY)
            )

RETURN
   sumx(DayDiff,[DayDiff])

You do not have to use the Country, Location and Month in the filter (in above dax) as they will be available in filter context you use (for e.g. PivotTable).

Please paste sample rows if this do not work.

DAX obtaining time series from Start and End Date

1 Answers