Forum Discussion

Bosh's avatar
Bosh
New Member
6 years ago
Solved

Create Billing Periods

Good morning. Among the data I enter into Power BI is a dd-mm-yy date column. But my final reports require an analysis based on "Billing Periods" that correspond from 26 month1 to 25 month2. That i...
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi Bosh ,

     

    We can create a calculate column to meet your requirement.

     

    Column = 
var _month =MONTH('Table'[Date])
var _next_month = _month + 1
var _current_date = DATE(YEAR('Table'[Date]),_month,26)
var _next_date = DATE(YEAR('Table'[Date]),_next_month,25)
var _format1 = FORMAT(_next_date,"mmm")&" "&YEAR('Table'[Date])
var _format2 = FORMAT(_current_date,"mmm")&" "&YEAR('Table'[Date])
return
IF('Table'[Date]<_next_date && 'Table'[Date]>=_current_date,_format1,_format2)

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the pbix that we have shared?

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    BTW, pbix as attached.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Bosh - Those same functions are in DAX, MONTH, DATE, YEAR, etc. I would probably create a custom column in a date table where you would calculate the billing period for the dates. You could then use that in a slicer.