Forum Discussion
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 ...
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
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
Hi Anonymous,
Thank you, worked like a charm.
Regards,
Mazigazi