Forum Discussion
RobinNeven
4 years agoHelper I
Make DAX code dynamic instead of hard-coded
Dear community, How do I make the following code dynamic so that I don't have to change the hard-coded values for yearweek (202053 & 202101) every year (sorry for the Dutch in it)? Inflow ja...
RobinNeven
4 years agoHelper I
Hi, thanks for your reply! So if I understand correctly, you're saying the following:
- Add a number column (let's call it 'Contract peilweek consecutive') with a number per week starting from the beginning of the date table ('Contract peildatum') and running over all the years in the date table. So if the calendar holds three years (2019-2021) this column will have the numbers 1-158 (52+53+53).
- Have a column in the date table which numbers the weeks belonging to the same year 1, 2, 3,..., 52/53 for each year. This we have and it's called 'Contract peilweek' in 'Contract peildatum'. Alternatively we could add an indicator column, but that constitutes more work so let's try the first option first.
- Rewrite measure 'Inflow jaarweek v4' to:
Inflow jaarweek v5 =
IF (
SUM ( Contracthistorie[IsInflowWeek] ) <> BLANK (),
IF (
SELECTEDVALUE ( 'Contract peildatum'[Contract peilweek consecutive] )
= MAX ( 'Contract peildatum'[Contract peilweek consecutive] ),
CALCULATE (
SUM ( Contracthistorie[IsInflowWeek] ),
FILTER (
ALL ( 'Contract peildatum' ),
'Contract peildatum'[Contract peilweek consecutive] = 'Contract peildatum'[Contract peilweek consecutive] + 1
)
),
CALCULATE (
SUM ( Contracthistorie[IsInflowWeek] ),
FILTER (
ALL ( 'Contract peildatum' ),
'Contract peildatum'[Contract peilweek consecutive]
= MIN ( 'Contract peildatum'[Contract peilweek consecutive] ) + 1
),
'Contract peildatum'[Is start contract peilweek] = 1
)
),
BLANK ()
)
Is this correct?