Calculate Due date / Statement Date

[gauravnarchal]

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

[v-kelly-msft · Accepted solution]

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,
Kelly

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

[lkalawski]

Hi @gauravnarchal 

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! 😀

[gauravnarchal]

@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

[lkalawski]

Hi @gauravnarchal

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! 😀

[gauravnarchal]

@lkalawski - I have to use measure instead of calculated column. Can you please help!

[lkalawski]

@gauravnarchal 

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! 😀