Forum Discussion
Solved
Calculate Due date / Statement Date
Hello All I need your help in creating a measure from the below table. Requirment If statement group is "M" result = EOMONTH + 1 + credit days Example If statement group is "M" & Credit d...
Hi gauravnarchal ，
Create a measure as below:
Measure = SWITCH ( MAX ( 'Table'[statement group] ), "M", EOMONTH ( MAX ( 'Table'[invoice date] ), 0 ) + 1 + MAX ( 'Table'[credit days] ), "F", IF ( DAY ( MAX ( 'Table'[invoice date] ) ) <= 15, DATE ( YEAR ( MAX ( 'Table'[invoice date] ) ), MONTH ( MAX ( 'Table'[invoice date] ) ), 15 ) + 1 + MAX ( 'Table'[credit days] ), EOMONTH ( MAX ( 'Table'[invoice date] ), 0 ) + 1 + MAX ( 'Table'[credit days] ) ) )And you will see:
For the related .pbix file ,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
lkalawski - Please find below data and thank you for your help in advance.
Note :- statement group & credit days values are from the Lookupvalue in the below table.
| invoice date | Name | Company | Active | statement group | credit days |
| 20-Jul-20 | User1 | ABC | FALSE | M | 30 |
| 25-Jul-20 | User2 | ABC | FALSE | M | 30 |
| 30-Jul-20 | User3 | ABC | TRUE | M | 30 |
| 04-Aug-20 | User4 | ABC | TRUE | M | 30 |
| 09-Aug-20 | User5 | ABC | FALSE | M | 30 |
| 10-Jun-20 | User1 | ABC123 | FALSE | F | 45 |
| 20-Jun-20 | User2 | ABC123 | FALSE | F | 45 |
| 30-Jun-20 | User3 | ABC123 | TRUE | F | 45 |
| 10-Jul-20 | User4 | ABC123 | TRUE | F | 45 |
| 20-Jul-20 | User5 | ABC123 | FALSE | F | 45 |
| 30-Jul-20 | User6 | ABC123 | FALSE | F | 45 |
| 09-Aug-20 | User7 | ABC123 | FALSE | F | 45 |
| 19-Aug-20 | User8 | ABC123 | TRUE | F | 45 |
| 10-Jun-20 | User1 | TEST1 | TRUE | M | 15 |
| 20-Jun-20 | User2 | TEST1 | FALSE | M | 15 |
| 30-Jun-20 | User3 | TEST1 | FALSE | M | 15 |
| 10-Jul-20 | User4 | TEST1 | FALSE | M | 15 |
| 20-Jul-20 | User5 | TEST1 | TRUE | M | 15 |
| 30-Jul-20 | User6 | TEST1 | TRUE | M | 15 |
| 09-Aug-20 | User7 | TEST1 | FALSE | M | 15 |
| 19-Aug-20 | User8 | TEST1 | FALSE | M | 15 |
| 29-Aug-20 | User9 | TEST1 | FALSE | M | 15 |
| 08-Sep-20 | User10 | TEST1 | TRUE | M | 15 |
You can add a calculated column:
Conditional =
SWITCH (
Tabele[statement group],
"M",
ENDOFMONTH ( Tabele[invoice date] ) + 1 + Tabele[credit days],
"F",
SWITCH (
TRUE (),
DAY ( Tabele[invoice date] ) <= 15,
DATE ( YEAR ( Tabele[invoice date] ), MONTH ( Tabele[invoice date] ), 15 ) + 1 + Tabele[credit days],
ENDOFMONTH ( Tabele[invoice date] ) + 1 + Tabele[credit days]
)
)
Let me know if you have to use measure instead of calculated column.
_______________
If I helped, please accept the solution and give kudos! 😀
lkalawski - I have to use measure instead of calculated column. Can you please help!
Please use it:
Conditional_2 = VAR _invoicedate = SELECTEDVALUE(Tabele[invoice date]) VAR _statementgroup = SELECTEDVALUE(Tabele[statement group]) -- You need adjust this variable. If statementgroup is not a static column then remove selectedvalue VAR _creditdays = SELECTEDVALUE(Tabele[credit days]) -- You need adjust this variable. If creditdays is not a static column then remove selectedvalue RETURN SWITCH(_statementgroup, "M", EOMONTH(_invoicedate,0) + 1 + _creditdays, "F", SWITCH(TRUE(), DAY(_invoicedate) <= 15, DATE(YEAR(_invoicedate), MONTH(_invoicedate),15) + 1 + _creditdays, EOMONTH(_invoicedate,0) + 1 + _creditdays ))
_______________
If I helped, please accept the solution and give kudos! 😀