Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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())
Solved! Go to 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
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.
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!
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |