DAX - Generate

[RobbeVL90]

Hi, I have a Calculated Table that seems to be working almost fine : 

EVALUATE
	VAR Today = TODAY()
	VAR MaxTerms = DATEDIFF(
		MIN('DIM_SalesforceProject'[GoLiveDate]),
		Today,
		YEAR
	)
	RETURN
		ADDCOLUMNS(
			GENERATE(
				GENERATESERIES(
					1,
					MaxTerms
				),
				SELECTCOLUMNS(
					'DIM_SalesforceProject',
					"Contract_StartDate",
					'DIM_SalesforceProject'[GoLiveDate],
					"ProjectCode",
					'DIM_SalesforceProject'[ProjectCode]
				)
			),
			"Term_StartDate", DATEADD(
				'DIM_SalesforceProject'[GoLiveDate],
				[Value] - 1,
				YEAR
			),
			"Term_EndDate", DATEADD(
				'DIM_SalesforceProject'[GoLiveDate],
				[Value] ,
				YEAR
			) - 1
		)

The result of this is the following : 

For some reason, future dates are not showing. 
Its like the "Value" Column  is returning Blank, but I dont get why. 

Any Help ? 

[Anonymous · Accepted solution]

Hi @RobbeVL90 ,

Please update the formula of calculated table as below and check if it can return the expected result... Please find the details in the attachment.

EVALUATE
VAR Today =
    TODAY ()
VAR MaxTerms =
    DATEDIFF ( MIN ( 'DIM_SalesforceProject'[GoLiveDate] ), Today, YEAR )
RETURN
    ADDCOLUMNS (
        GENERATE (
            GENERATESERIES ( 1, MaxTerms ),
            SELECTCOLUMNS (
                'DIM_SalesforceProject',
                "Contract_StartDate", 'DIM_SalesforceProject'[GoLiveDate],
                "ProjectCode", 'DIM_SalesforceProject'[ProjectCode]
            )
        ),
        "Term_StartDate",
            EDATE ( [Contract_StartDate], ( [Value] - 1 ) * 12 ),
        "Term_EndDate",
            EDATE ( [Contract_StartDate], [Value] * 12 ) - 1
    )

Best Regards

[bhelou]

try this : 

EVALUATE
VAR Today = TODAY()
VAR MaxTerms = DATEDIFF(
MIN('DIM_SalesforceProject'[GoLiveDate]),
Today,
YEAR
)
RETURN
ADDCOLUMNS(
GENERATE(
GENERATESERIES(
1,
MaxTerms
),
SELECTCOLUMNS(
'DIM_SalesforceProject',
"Contract_StartDate", 'DIM_SalesforceProject'[GoLiveDate],
"ProjectCode", 'DIM_SalesforceProject'[ProjectCode]
)
),
"Value", [Value],
"Term_StartDate", DATEADD(
'DIM_SalesforceProject'[GoLiveDate],
[Value] - 1,
YEAR
),
"Term_EndDate", DATEADD(
'DIM_SalesforceProject'[GoLiveDate],
[Value],
YEAR
) - 1
)

[RobbeVL90]

Only throws an obvious error. 
I cannot refer to a colum added. 
For some reason Go Live Date is "blank" for future dates...

[bhelou]

hi , please share some sample of the pbix to go more

[RobbeVL90]

Hi,

Its fairly straight forward. 
Its just 2 values like my exampe : 
"GoLiveDate"  & "PartnerCode"

01/07/2022

0ngNDS2zvw