Forum Discussion
Need remark dates in one column based on another column.
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 |
|
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.
8 Replies
- parry2k
Super User
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.
Thank you so much. It works.
- 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.
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.
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 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.
