Accrual Basis Rows splitting

[Shubhshrsth143]

I have connected database to power bi. i need to calculate accrual amount on the basis of start and expiry date of transaction splitted in month column every month. I have a file that does the same in excel.
But i want this to happen in power bi. 
Could anyone suggest how can i automatically generate column for each month of different years to split the amount according the start date and expiry date without any manual intervention afterwards. Data is huge.
This calculation takes more that 1 hour in excel after getting the dump.
But if this would be possible in power bi the work will get more easier and cross checking and visuals can be more effective.
Please suggest how can i split each transaction amount to different month accrording to start date .
For eg: a man bought a package for Rs.3000 on 15-july-2024 for 1 year. so expiry would be 14-july-2025. Amount must be splitted in a way that. 2024 july 125, august 250, september 250 and so on to july 2025 which will have Rs.125 splitted. This split is required automatic.
I hope this would not take long for the experts in this forum. Hoping to see the solution as soon as possible.

[DataNinja777]

Hi @Shubhshrsth143 ,

Accrued expenses are expenses that have been incurred but not yet paid; they are typically settled in the short term and removed from the balance sheet when paid or invoices are received and reclassifed to AP.

You can produce your required output of the accrued expenses which gets recorded with the passage of time by writing a dax measure like below:

Accrued expense recognition = 
SUMX(
    'Accrued expenses',
    VAR StartDate = MAX('Accrued expenses'[Start date])
    VAR EndDate = MIN('Accrued expenses'[End date])
    
    // Get the number of days within the current month filter context
    VAR DaysInMonth = COUNTROWS(
        FILTER(
            'Calendar',
            'Calendar'[Date] >= StartDate &&
            'Calendar'[Date] <= EndDate &&
            'Calendar'[Date] >= STARTOFMONTH('Calendar'[Date]) &&
            'Calendar'[Date] <= ENDOFMONTH('Calendar'[Date])
        )
    )
    
    RETURN
        IF(
            StartDate <= MAX('Calendar'[Date]) &&
            EndDate >= MIN('Calendar'[Date]),
            'Accrued expenses'[Daily expense] * DaysInMonth,
            BLANK()
        )
)

 Which will produce the visualization like below:

Additionally, cumulative accrued expense until invoice is received, will look like below:

Accrued expense cumulative = 
SUMX(
    'Accrued expenses',
    VAR StartDate = 'Accrued expenses'[Start date]
    VAR EndDate = 'Accrued expenses'[End date]

    // Calculate the number of days from the start date up to the end date, within the cumulative range
    VAR DaysInRange = COUNTROWS(
        FILTER(
            ALL('Calendar'),
            'Calendar'[Date] >= StartDate &&
            'Calendar'[Date] <= EndDate &&
            'Calendar'[Date] <= MAX('Calendar'[Date]) // Cumulative up to the current calendar date in context
        )
    )

    RETURN
        IF(
            StartDate <= MAX('Calendar'[Date]) && EndDate >= MIN('Calendar'[Date]),
            'Accrued expenses'[Daily expense] * DaysInRange,
            BLANK()
        )
)

I've attached an example pbix file for your reference.

Best regards,

[chiranjeevikudu]

To split transaction amounts across months in Power BI based on a start and end date:

1. Create a Date Table: Ensure it includes every month within your date range.
2. Generate Monthly Values:
   • Use DAX to create a calculated column that distributes the amount proportionally across months based on days within each period.
3. Set Up Relationship: Link your transaction table to the date table, enabling automatic updates and visuals.

Try this approach, and let me know if it helps!

---

Chiranjeevi Kudupudi
Power Platform Expert
LinkedIn: Chiranjeevi Kudupudi

If this answer helped, please like it!

[danextian]

Hi @Shubhshrsth143 

Questions:

How big is the data?

Does your table have  start and end dates?

How would you split an amount with a start date that isn't 1sth and 15th of the month, say the 5th?

[danextian]

Please see this sample M code

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdDNDcAgCAbQXTg3KT8KMovp/mtUbZPipdyEPPGT3kEQEQ6geqKdjFxmUd6iwnV0IBYeXWyBjFMkPEy8uKbIRhpVTaa4KydZCnkeV11TY89TP4HJpKRx5Pm3RlI3IoZtdi0S3V9S9GXibmjfDRG7z7ZH0z5z3Q==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Amount = _t, Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Amount", Int64.Type}, {"Start", type date}, {"End", type date}}),
    #"Inserted Days in Month" = Table.AddColumn(#"Changed Type", "Days in Month", each Date.DaysInMonth([End]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Days in Month", "Number of Months", each Number.Round(Number.From(([End] - [Start])/( 365.25 / 12 )) ,0 )),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Number of Months", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Period", each {0..[Number of Months]-1}),
    #"Expanded Period" = Table.ExpandListColumn(#"Added Custom1", "Period"),
    #"Added Custom2" = Table.AddColumn(#"Expanded Period", "Split1", each let 
monthly = [Amount]/[Number of Months]
in
if [Period] > 0 and [Period] <[Number of Months]-1 then monthly 
else if [Period] = 0 then ( ([Days in Month] - Date.Day([Start]) + 1 )/[Days in Month]) * monthly else 
( (Date.Day([End])/[Days in Month]) * monthly )),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Split2", each let 
monthly = [Amount]/[Number of Months]
in
if [Period] > 0 and [Period] <[Number of Months]-1 then monthly 
else monthly/2),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"Period", Int64.Type}, {"Split1", type number}, {"Split2", type number}})
in
    #"Changed Type2"