Forum Discussion
"Dynamic" Commutative Totals
Hello all!
I have a bit of a specific scenario, which has proven to be a bit too much for my limited knowledge of DAX. I don't even know if I named this thread correctly.
I want to present the number of active jobs (Line chart or Candlestick chart) (Jobs are tasks with the set duration, that are completed by the network) at a specific point in time.
I have the following columns in table "offers 1":
- offer_id (unique ID for each job);
- holding_in_minutes (Duration);
- Job_Start_Date;
- Job_End_Date;
- status (was the job accepted or not "FINALIZED"=Accepted (Interested in these only) and "CREATED"= Not accepted)
- JobCount (There were some changes made to DB so jobs finished before December 2019 are missing offer_id).
Currently, I am using the following two measures to do so:
New Jobs =
SUM('offers 1'[JobCount])Commutative Active Jobs =
CALCULATE([New Jobs],
FILTER(ALLSELECTED('offers 1'),
'offers 1'[Job_Start_Date]<=MAX('offers 1'[Job_Start_Date])),
FILTER(ALLSELECTED('offers 1'), 'offers 1'[status]="FINALIZED"),
FILTER(ALLSELECTED('offers 1'), NOW()<='offers 1'[Job_End_Date].[Date]))However, correct me if I am wrong, but this will ignore jobs that have already finished from counting. What I would like to see is for example:
If I look at the chart on the 5th of January and there are 40 active jobs on that day, but 10 of them will be finished on that day and 2 new will be accepted the next day, the 6th of January will show 32 jobs.
I hope this is good enough of an explanation for what I need.
Thank you for the help guys, cheers.
- Anonymous6 years ago
Hi Mazigazi,
I'd like to suggest you create a calendar table with the whole date ranges across your table and write a formula to calculate the cumulative total based on the current calendar date. (notice: use not related calendar date as the axis of your visual)
Calendar:
Calendar = CALENDAR ( MIN ( Table[Job_Start_Date] ), MAX ( Table[Job_End_Date] ) )Measure:
Commutative Active Jobs = VAR currDate = MAX ( 'Calendar'[Date] ) RETURN CALCULATE ( [New Jobs], FILTER ( ALLSELECTED ( 'offers 1' ), 'offers 1'[status] = "FINALIZED" && 'offers 1'[Job_Start_Date] <= currDate && 'offers 1'[Job_End_Date] >= currDate ) )Regards,
Xiaoxin Sheng
2 Replies
- AnonymousNot applicable
Hi Mazigazi,
I'd like to suggest you create a calendar table with the whole date ranges across your table and write a formula to calculate the cumulative total based on the current calendar date. (notice: use not related calendar date as the axis of your visual)
Calendar:
Calendar = CALENDAR ( MIN ( Table[Job_Start_Date] ), MAX ( Table[Job_End_Date] ) )Measure:
Commutative Active Jobs = VAR currDate = MAX ( 'Calendar'[Date] ) RETURN CALCULATE ( [New Jobs], FILTER ( ALLSELECTED ( 'offers 1' ), 'offers 1'[status] = "FINALIZED" && 'offers 1'[Job_Start_Date] <= currDate && 'offers 1'[Job_End_Date] >= currDate ) )Regards,
Xiaoxin Sheng
- MazigaziNew Member
Hi Anonymous,
Thank you, worked like a charm.
Regards,
Mazigazi