Make DAX code dynamic instead of hard-coded

[RobinNeven]

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 jaarweek v3 = 
IF (
    SUM ( Contracthistorie[IsInflowWeek] ) <> BLANK (),
    IF (
        SELECTEDVALUE ( 'Contract peildatum'[Contract peiljaarweek] ) = 202053,
        CALCULATE (
            SUM ( Contracthistorie[IsInflowWeek] ),
            FILTER (
                ALL ( 'Contract peildatum' ),
                'Contract peildatum'[Contract peiljaarweek] = 202101
            )
        ),
        CALCULATE (
            SUM ( Contracthistorie[IsInflowWeek] ),
            FILTER (
                ALL ( 'Contract peildatum' ),
                'Contract peildatum'[Contract peiljaarweek]
                    = MIN ( 'Contract peildatum'[Contract peiljaarweek] ) + 1
            ),
            'Contract peildatum'[Is start contract peilweek] = 1
        )
    ),
    BLANK ()
)

I came up with the following, which takes into account all the different data types as well:

Inflow jaarweek v4 = 
IF (
    SUM ( Contracthistorie[IsInflowWeek] ) <> BLANK (),
    IF (
        CONVERT (
            SELECTEDVALUE ( 'Contract peildatum'[Contract peiljaarweek] ),
            STRING
        )
            = CONCATENATE (
                FORMAT ( YEAR ( TODAY () ) - 1, "yyyy" ),
                MAX ( 'Contract peildatum'[Contract peilweek] )
            ),
        CALCULATE (
            SUM ( Contracthistorie[IsInflowWeek] ),
            FILTER (
                ALL ( 'Contract peildatum' ),
                CONVERT ( 'Contract peildatum'[Contract peiljaarweek], STRING )
                    = CONCATENATE (
                        FORMAT ( VALUE ( 'Contract peildatum'[Contract peiljaar] ) + 1, "yyyy" ),
                        "01"
                    )
            )
        ),
        CALCULATE (
            SUM ( Contracthistorie[IsInflowWeek] ),
            FILTER (
                ALL ( 'Contract peildatum' ),
                'Contract peildatum'[Contract peiljaarweek]
                    = MIN ( 'Contract peildatum'[Contract peiljaarweek] ) + 1
            ),
            'Contract peildatum'[Is start contract peilweek] = 1
        )
    ),
    BLANK ()
)

There are no syntax errors, but it doesn't give the desired result either, in which the right column below should be equal to the left column. For everything that's crossed out, the numbers are the same, but for yearweek '202053', 'Inflow jaarweek v4' stays empty (but should contain 24,120).

Thanks a lot in advance!

[Anonymous]

Inflow jaarweek v5 =
IF(
	// Blank is treated as 0 in ADX,
	// so (BLANK = 0) is true but
	// (BLANK == 0) is false.
    SUM( Contracthistorie[IsInflowWeek] ) <> 0,
    
    var CurrentSeqNo =
    	SELECTEDVALUE( 'Dates'[MonthSeqNo] )
    var IsLastWeekInYear =
    	int( SELECTEDVALUE( 'Dates'[MonthSeqNoInYear] ) = 53 )
   	var Result = 
		CALCULATE(
		    SUM( Contracthistorie[IsInflowWeek] ),
		    'Dates'[MonthSeqNo] = CurrentSeqNo + 1,
		    // If it's true that the field
		    // [Is start contract peilweek] can only
		    // hold 1 and 0 (as it should be since
		    // it's a boolean column), then this
		    // whole formula can be reduced to
		    // what you see here. Please make sure
		    // you don't store any BLANKs in the
		    // column. And if the model is correct,
		    // you should not have BLANKs here.
		    'Dates'[Is start contract peilweek]
		    	in {1, 1 - IsLastWeekInYear},
		   	ALL( 'Dates' )
	    )
	RETURN
		Result
)

[RobinNeven]

Hi @Anonymous ,

Thanks for your reply! I tried a couple of variations of your code trying to come up with the right one. But haven't figured it out just quite yet.

I'm assuming that where you say 'MonthSeqNoInYear' you actually mean 'WeekSeqNoInYear' given that it's followed by "= 53"? And in that same fashion I think you mean 'WeekSeqNo' instead of 'MonthSeqNo' elsewhere in the code?

If so, it would look like the following to use the right field names for my model:

Inflow jaarweek v6 = 
IF(
	// Blank is treated as 0 in ADX,
	// so (BLANK = 0) is true but
	// (BLANK == 0) is false.
    SUM( Contracthistorie[IsInflowWeek] ) <> 0,
    
    var CurrentSeqNo =
    	SELECTEDVALUE( 'Contract peildatum'[Contract peilweek order] )
    var IsLastWeekInYear =
    	int( SELECTEDVALUE( 'Contract peildatum'[Contract peilweek order]) = 53 )
   	var Result = 
		CALCULATE(
		    SUM( Contracthistorie[IsInflowWeek] ),
		    'Contract peildatum'[Contract peilweek order] = CurrentSeqNo + 1,
		    // If it's true that the field
		    // [Is start contract peilweek] can only
		    // hold 1 and 0 (as it should be since
		    // it's a boolean column), then this
		    // whole formula can be reduced to
		    // what you see here. Please make sure
		    // you don't store any BLANKs in the
		    // column. And if the model is correct,
		    // you should not have BLANKs here.
		    'Contract peildatum'[Is start contract peilweek]
		    	in {1, 1 - IsLastWeekInYear},
		   	ALL( 'Contract peildatum' )
	    )
	RETURN
		Result
)

And this works in the sense that it gives no errors and gives back the same results as my v4 version of the measure. But it doesn't give the result that i'm looking for similar to v3 (but dynamically) unfortunately. Am I missing something?

Thanks again!

[daxer-almighty]

Since I don't know your model and all I've been doing is guessing as best as I could, it might be that you need to adjust something in the measure I gave you to make it work in your case. I can't help you more than I have due to me not knowing your model and data. All you have to do is take the framework I've shown here and make it suitable for your case.

[RobinNeven]

Hi, thanks for your reply! So if I understand correctly, you're saying the following:

1. 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).
2. 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.
3. 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?

[daxer-almighty]

It's very easy in fact. You just have to number all your weeks consecutively 1, 2, 3... and use this number instead of the form YYYYMM. Also, you should number the weeks belonging to the same year 1, 2, 3,..., 52/53 for each year. Then the IF will sense if you're in the last week of the year (even better: instead of the latter numbering create an indicator column that will tell you if you're on the last week of the year) and all you'll have to do is to advance the first consecutive number by 1 if you find you're on the last week of the year. That's fully dynamic.

If all the years have the same number of weeks in them, say 53, you can also use the clock arithmetic to simplify the formulas. You wouldn't need any IF for this to work if you do your calculations modulo 53.