active jobs over time with filters

[Anonymous]

Please help met to get the active jobs over time, while still able to apply filters on the power bi dashboard (so no hardcoding stuff).

I have the following tables:

VW

JobOpenedDate    JobClosedDate     FilterX     FilterY
03/04/2018            06/06/2019           1              0
21/12/2018            07/11/2019           0              1

DateTimeTable
DateTime

I build relationships between

* DateTime and JobOpenedDate

* DateTime and JobClosedDate

I calculated a measure called ExtraJobsPerPeriod, which is based on the following DAX:

ExtraJobsPerPeriod = IF (
ISNUMBER([OpenedJobs] - [ClosedJobs]);
[OpenedJobs] - [ClosedJobs];
0)

The OpenedJobs and ClosedJobs are measures which are calculated as follows:

OpenedJobs = IF (
CALCULATE(COUNT('VW'[JobOpenedDate]);USERELATIONSHIP('VW'[JobOpenedDate];DateTimeTable[DateTime])) > 0;
CALCULATE(COUNT('VW'[JobOpenedDate]);USERELATIONSHIP('VW'[JobOpenedDate];DateTimeTable[DateTime]));
0)

ClosedJobs = IF (
CALCULATE(COUNT('VW'[JobClosedDate]);USERELATIONSHIP('VW'[JobClosedDate];DateTimeTable[DateTime])) > 0;
CALCULATE(COUNT('VW'[JobClosedDate]);USERELATIONSHIP('VW'[JobClosedDate];DateTimeTable[DateTime])); 
0)

If you then plot ExtraJobsPerPeriod over DateTime, you get exactly the number of additional jobs that are active per time period. Now I just want to create a cumulative sum / running total of this measure, but that is where it goes wrong. Please help me to improve the following formula for "RunningTotal", which gives bad results (somehow, it calculates a running total for 1 year only, and then it starts over again....)

RunningTotal = SUMX(
FILTER(
    ALL('DateTimeTable');
    AND(DateTimeTable[DateTime].[MonthNo]<=MAX(DateTimeTable[DateTime].[MonthNo]);
    DateTimeTable[DateTime].[YEAR]<=MAX(DateTimeTable[DateTime].[Year]))
    );
[ExtraJobsPerPeriod])

Please help me to fix this RunningTotal DAX formula because it is wrong, and I would be so happy 🙂

[RobbeVL]

Hi there,

I think this will work when you use the lowest date in the hierarchy.

Please give the dax code below a shot.

CALCULATE(
	Measure1;
	FILTER(
		ALLSELECTED('Table'[Date]);
		ISONORAFTER('Table'[Date]; MAX('Table'[Date]); DESC)

[Anonymous]

Thanks for your quick response RobbeVL.

Unfortunately, your code is not doing what I want since it leaves gaps where there are no data points. So for some months the data is correct, and for other months it gives 0, instead of the cumulative sum.

[RobbeVL]

Hi,

Strange.
Any chance you could provide some sort of sample dataset or pbix file ? 

Robbe

[Anonymous]

I think it is because of the relation between tables, specifically between columns Datetime and JobOpenedDate.

Suppose we have the following data:

JobOpenedDate       JobClosedDate

21/03/2019               11/10/2019

03/04/2019               11/10/2019

06/06/2019               11/10/2019

29/09/2019               11/10/2019

DateTimeTable

01/03/2019

02/03/2019

.......................

29/11/2019

30/11/2019

Then the powerbi graph displays the following:

March 2019: 1 Active Job

April 2019: 2 Active Jobs

May 2019: 0 Active Jobs (because no relation between tables can be made)
June 2019: 3 Active Jobs

July 2019: 0 Active Jobs (because no relation between tables can be made)

August 2019: 0 Active Jobs (because no relation between tables can be made)

September 2019: 4 Active Jobs

October 2019: 0 Active Jobs (because no relation between tables can be made, but in this case it is actually correct!)

So when there is no job opened in a specific month, the graph displays wrong results for that month.