Forum Discussion

gauravnarchal's avatar
gauravnarchal
Post Prodigy
6 years ago
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...
Need Help
Show and Tell
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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's avatar
lkalawski
Resident Rockstar
6 years ago

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's avatar
    gauravnarchal
    Post Prodigy
    6 years ago

    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

     

    • lkalawski's avatar
      lkalawski
      Resident Rockstar
      6 years ago

      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's avatar
        gauravnarchal
        Post Prodigy
        6 years ago

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