Need remark dates in one column based on another column.

[ritar_li]

Hi,

Our billing team billed services for Jan and Feb together on 3/31/2022. Now I need to separate those services occurred in Jan from services completed in Feb.

First step, I convert the “complete date” to a “MM-YYYY”. Then I write a DAX:

Billing date-mod =

if('table'[Completion date MM-YYYY]="1-2022"&&('table'[Billing Date]=DATE(2022,3,31)),

DATE(2022,02,15),

'table'[Billing Date])

Unfortunately, in the column of Billing date-mod, all cells are 3/31/2022. I need January service billing date is 2/15, and the rest keeps same as billing date.

Anyone can help me to troubleshoot the problem? 

I greatly appreciate your help. 

Service ID	Complete date	Billing date	Complete Date-MM-YYYY	Billing date-mod
1	1/5/2022	3/31/2022	1-2022
2	1/5/2022	3/31/2022	1-2022
3	1/7/2022	3/31/2022	1-2022
4	1/10/2022	3/31/2022	1-2022
5	2/5/2022	3/31/2022	2-2022
6	2/10/2022	3/31/2022	2-2022
7	2/10/2022	3/31/2022	2-2022
8	2/15/2022	3/31/2022	2-2022

[parry2k · Accepted solution]

@ritar_li good to hear, accept the solutions so that others can take advantage of this as well.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[parry2k]

@ritar_li I think this is what you are looking for:

New Column = 
IF ( EOMONTH ( Table[Completed Date], 0 ) = DATE(2022,1,31) && Table[Billing Date] = DATE(2022,03,31 ), DATE(2022,2,15), Table[Billing Date] )

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[ritar_li]

Thank you so much. It works. 

[ritar_li]

Hi,

I'm sorry for confusion. 

I want to create a billing date column.

Some service charges, which were completed in the beginning of Jan 2022, were invoiced on 1/26/2022. I want to keep those services' billing dates as 1/26/2022. I want services which completed in Jan 2022 and billed on 3/31 use billing date 2/15/2022. The rest of billing dates keep as it is. 

Hope I make my question is clear. 😀

Thanks again.

[parry2k]

@ritar_li what do you want to happen in this case? Can you explain the full logic?

[parry2k]

@ritar_li try this calculated column:

New Column = 
IF ( EOMONTH ( Table[Completed Date], 0 ) = DATE(2022,1,31), DATE(2022,2,15), Table[Billing Date] )

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[ritar_li]

Thank you so much! It works perfectly. 

However, I found new problem. Billing team also send some invoices in 1/26/2022. I listed in a new table below. If I use the DXA you provided, the total charges/per month shift. 

How can I fix this problem?

Service ID	Complete date	Billing date	Billing date-mod
1	1/5/2022	3/31/2022
2	1/5/2022	3/31/2022
3	1/7/2022	3/31/2022
4	1/10/2022	3/31/2022
5	2/5/2022	3/31/2022
6	2/10/2022	3/31/2022
7	2/10/2022	3/31/2022
8	2/15/2022	3/31/2022
9	1/6/2022	1/26/2022
10	1/2/2022	1/26/2022
11	1/4/2022	1/26/2022

[Ashish_Mathur]

Hi,

Clearly show the expected result in another column.