topic Column that will return payments made 2 months earlier in DAX Commands and Tips

Column that will return payments made 2 months earlier

Anonymous — Thu, 09 Jul 2020 07:26:47 GMT

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())

Re: Column that will return payments made 2 months earlier

Anonymous — Thu, 09 Jul 2020 09:34:25 GMT

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!

Re: Column that will return payments made 2 months earlier

Anonymous — Thu, 09 Jul 2020 09:40:59 GMT

Hi Anonymous

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.

Re: Column that will return payments made 2 months earlier

Anonymous — Thu, 09 Jul 2020 09:49:06 GMT

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

Re: Column that will return payments made 2 months earlier

Anonymous — Thu, 09 Jul 2020 10:03:46 GMT

Anonymous Thank you very much.

B.R.

M.R.

Re: Column that will return payments made 2 months earlier

Anonymous — Wed, 22 Jul 2020 09:31:24 GMT

Anonymous

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.