Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Adjust/customize the billing date

Hi Power BI Community,

I have this data which involves date. The thing is, our reporting month should be within our billing date.
Our billing date is from 26th of the previous month upto 25th of the current month.

 

BeforeAfter
12/26/201901/01/2020
12/27/202001/02/2020
.
.
.		.
.
.
01/26/202002/01/2020
01/27/202002/02/2020
.
.
.		.
.
.
12/25/202012/31/2020

Take the table above for example. I want it that way.
I found some solution but not exactly what i want.
can anyone help me out?

Thanks

Need Help

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    5 years ago

    Hi Anonymous ,

     

    I'm not sure I've understood your requirement fully, but if you want to add a reporting date that is the first of the following month if the 'Before' date is before the 26th, but the first of the month-after-next if it is on or after the 26th, then add this as a new column in Power Query:

     

    if Date.Day([Before]) >= 26
then Date.StartOfMonth(Date.AddMonths([Before], 2))
else Date.StartOfMonth(Date.AddMonths([Before], 1))

     

     

    This is the output I get:

     

    It doesn't follow the 'After' column example, but that column didn't seem to follow any particular pattern that I could see in relaion to the 'Before' state.

     

    Pete