Forum Discussion
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 days is 30
invoice date is 23rd Aug
result = 31st Aug (EOMONTH) + 1(NEXT Day) + 30(Credit days) = 30th Sep
If Statement group is "F"
and
if invoice date is =<15
result = 15th Day + 1 + Credit Days
Example
If statement group is "F" & Credit days is 30
invoice date is 14th Aug
result = 15th Aug (Day 15) + 1(NEXT Day) + 30(Credit days) = 15th Sep
if invoice date > 15
result = EOMONTH + 1 + credit days
Example
If statement group is "F" & Credit days is 45
invoice date is 24th Aug
result = 31st Aug (EOMONTH) + 1(NEXT Day) + 45(Credit days) = 15th Oct
*statement group and credit days are Lookvalue in the below table
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!
6 Replies
Can you add these sample of data by using this option:
It will be easier to prepare for you the proper formula.
But generally speaking, in this case it is best to use the SWITCH function.
_______________
If I helped, please accept the solution and give kudos! 😀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! 😀
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!
