Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
gauravnarchal
Post Prodigy
Post Prodigy

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

 

gauravnarchal_1-1598950104127.png

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

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:

Annotation 2020-09-03 163612.png

For the related .pbix file ,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

6 REPLIES 6
v-kelly-msft
Community Support
Community Support

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:

Annotation 2020-09-03 163612.png

For the related .pbix file ,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
lkalawski
Super User
Super User

Hi @gauravnarchal 

Can you add these sample of data by using this option:

lkalawski_0-1598951588756.png

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 dateNameCompanyActivestatement groupcredit days
20-Jul-20User1ABCFALSEM30
25-Jul-20User2ABCFALSEM30
30-Jul-20User3ABCTRUEM30
04-Aug-20User4ABCTRUEM30
09-Aug-20User5ABCFALSEM30
10-Jun-20User1ABC123FALSEF45
20-Jun-20User2ABC123FALSEF45
30-Jun-20User3ABC123TRUEF45
10-Jul-20User4ABC123TRUEF45
20-Jul-20User5ABC123FALSEF45
30-Jul-20User6ABC123FALSEF45
09-Aug-20User7ABC123FALSEF45
19-Aug-20User8ABC123TRUEF45
10-Jun-20User1TEST1TRUEM15
20-Jun-20User2TEST1FALSEM15
30-Jun-20User3TEST1FALSEM15
10-Jul-20User4TEST1FALSEM15
20-Jul-20User5TEST1TRUEM15
30-Jul-20User6TEST1TRUEM15
09-Aug-20User7TEST1FALSEM15
19-Aug-20User8TEST1FALSEM15
29-Aug-20User9TEST1FALSEM15
08-Sep-20User10TEST1TRUEM15

 

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

 

lkalawski_0-1598956148308.png

 

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!

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.