CODEWITHSUNDEEP
powerbidaxm
Peter Rundqvist
Peter Rundqvist

Reputation: 315

How to create measure that "SUMX" values up for a given end date?

I am have difficulties creating a measure that calculates the total sum of the values in a column Duration (h) for a given EndDate.

I have a simple table that looks like this:

Events = 
DATATABLE (
    "EventID"; INTEGER;
    "StartDate"; DATETIME;
    "EndDate"; DATETIME;
    "Interval"; INTEGER;
    "Duration (h)"; DOUBLE;
    {
        {1; "2018-01-01"; "2019-12-31"; 35; 1,45 };
        {2; "2019-02-14"; "2019-12-31"; 2; 0,05 };
        {3; "2019-06-01"; "2019-07-01"; 1; 0,05 }
    }
)

I have managed to create a calculated column that calculates the "Total hours" for each row in the table. I've done this in two steps:

First i create a calculated column:

Number of events in period =
DATEDIFF ( Events[StartDate]; Events[EndDate]; DAY ) / Events[Interval]

Then I create another calculated column:

Total number of hours in period =
Events[Number of events in period] * Events[Duration (h)]

But - what I need to create is a Measure that calculates the above, but for a given EndDate.

The formula should use the existing Events[EndDate] if the given date is beyond this date.

Like this:

E.g. with a given EndDate of 2019-09-01:

{1; "2018-01-01"; "2019-12-31"; 35; 1,45 }; --use 2019-09-01
{2; "2019-02-14"; "2019-12-31"; 2; 0,05 };  --use 2019-09-01
{3; "2019-06-01"; "2019-07-01"; 1; 0,05 }   --use 2019-07-01

I have tried to use the SUMX function, but I got stuck.

Does anyone know how to create this measure?

Kind regards, Peter

Upvotes: 2

Views: 739

Answers (1)

Marc Pincince
Marc Pincince

Reputation: 5192

I think this may do what you are looking for:

Measure = var otherEndDate = Date(2019,09,01) return SUMX(Events, DATEDIFF ( Events[StartDate], if(Events[EndDate] > otherEndDate, otherEndDate, Events[EndDate]), DAY ) / Events[Interval] * Events[Duration (h)])

I set a variable (otherEndDate) with a replacement date of 2019-09-01 to use if the existing EndDate exceeds it. The if statement handles the replacement.

Upvotes: 1

Related Questions