Forum Discussion

Mazigazi's avatar
Mazigazi
New Member
6 years ago
Solved

"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.

  • Anonymous's avatar
    Anonymous
    6 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

  • Anonymous's avatar
    Anonymous
    Not 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

    • Mazigazi's avatar
      Mazigazi
      New Member

      Hi Anonymous, 

      Thank you, worked like a charm.

      Regards,

      Mazigazi