Forum Discussion

RobbeVL90's avatar
RobbeVL90
Frequent Visitor
2 years ago
Solved

DAX - Generate

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 ? 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

5 Replies

  • bhelou's avatar
    bhelou
    Responsive Resident
    2 years ago

    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's avatar
      RobbeVL90
      Frequent Visitor
      2 years ago

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

      • bhelou's avatar
        bhelou
        Responsive Resident
        2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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