Column that will return payments made 2 months earlier

[Anonymous]

Hi,

I'm trying to improve my column, to eliminate necessity of using statement with actual date: "DATE(2020;04;01)"; because every month I have to change it. 

Input data will be always downloaded for last 3 months; and this column is to return values for current mont -2. 

Any idea how to do it?

Payments Received in April = IF('All'[Document type]="DZ";IF(AND('All'[Document Date]>=DATE(2020;04;01);'All'[Document Date]<DATE(2020;05;01));'All'[Amount in local currency];BLANK());BLANK())

[harshnathani · Accepted solution]

HI @Anonymous ,

Try this then

Payments Received =
VAR a =
    MONTH (
        TODAY ()
    )
VAR b =
    SWITCH (
        TRUE ();
        a = 3; 12;
        a = 2; 11;
        a = 1; 10;
        a
    )
RETURN
    IF (
        'All'[Document type] = "DZ";
        IF (
            AND (
                'All'[Document Date]
                    >= DATE ( 2020; b - 3; 01 );
                'All'[Document Date]
                    < DATE ( 2020; b - 2; 01 )
            );
            'All'[Amount in local currency];
            BLANK ()
        );
        BLANK ()
    )

Regards,

Harsh Nathani

[harshnathani]

HI @Anonymous ,

You can try this

Payments Received in April =
IF (
    'All'[Document type] = "DZ";
    IF (
        AND (
            'All'[Document Date]
                >= TODAY () - 90;
            'All'[Document Date]
                < TODAY () - 60
        );
        'All'[Amount in local currency];
        BLANK ()
    );
    BLANK ()
)

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @harshnathani 

Thank you for reply. 

Unfortunately, even if updated in the middle of the month, it must return only values from a single month. 

Thank you for your time. 

B.R. 
M.R.

[harshnathani · Accepted solution]

HI @Anonymous ,

Try this then

Payments Received =
VAR a =
    MONTH (
        TODAY ()
    )
VAR b =
    SWITCH (
        TRUE ();
        a = 3; 12;
        a = 2; 11;
        a = 1; 10;
        a
    )
RETURN
    IF (
        'All'[Document type] = "DZ";
        IF (
            AND (
                'All'[Document Date]
                    >= DATE ( 2020; b - 3; 01 );
                'All'[Document Date]
                    < DATE ( 2020; b - 2; 01 )
            );
            'All'[Amount in local currency];
            BLANK ()
        );
        BLANK ()
    )

Regards,

Harsh Nathani

[Anonymous]

@harshnathani Thank you very much. 

B.R.

M.R.

[Anonymous · Accepted solution]

@harshnathani 

Well actually I found this function to work better:

Sum of payments CM -2 = 
IF (
    'All'[Document type] = "DZ",
    IF (
        'All'[Document Date] > EOMONTH ( TODAY (), -3 )
            && 'All'[Document Date] <= EOMONTH ( TODAY (), -2 ),
        'All'[Amount in local currency],
        BLANK ()
    ),
    BLANK ()
)

Thank you. 

B.R.

M.R.