Power BI Data Access, Transformation and Cleansing

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.

Accruals base on contracts

  • 1.  Accruals base on contracts

    Posted 22 days ago
      |   view attached

    Hello everybody

    I wonder if anyone could give me some tips/help how could I solve this challenge using DAX formulas:

    I have a spreadsheet with the contracts conditions and a sample fact data ,in attachment 

    Contract details:

    Insurance Type Commission year 0 Commission year 1
    A Vida 0,3 0,25
    B Vida 0,3 0,15



    Fact Details :

    Insurance Type Amount StartDate Payment type
    A Vida 252 01/03/2018 Month
    A Vida 300 01/01/2019 Quarter
    A Vida 252 01/07/2020 Semester
    B Vida 400 01/01/2020 Year


    The goal will be to generate accruals base on Insurance contract year 0 or year 1 * Amount, split per payment type , monthly 1/12 per month , quarter 1/4 per quarter , 1/2 per semester ,1/1 year.
    Generate this accruals, lower lever year/month, not need the day,  from Startdate until today on payment type detail ,month, quarter, etc

    This will allow to know upfront what we will receive from the insurance company ,base on the payment type, and validate if what they are paying is what is expected.

    Sample of the data needed: (base of first entry of both tables, sample data)
    Month example 

    Inssurance Type Accrual Date
    A Vida 75,6 201801
    A Vida 75,6 201802
    A Vida 75,6 201803
    A Vida 75,6 201804
    A Vida 75,6 201805
    A Vida 75,6 201806
    A Vida 75,6 201807
    A Vida 75,6 201808
    A Vida 75,6 201809
    A Vida 75,6 201810
    A Vida 75,6 201811
    A Vida 75,6 201812
    A Vida 63 201901
    A Vida 63 201902
    A Vida 63 201903
    A Vida 63 201904
    A Vida 63 201905
    A Vida 63 201906
    A Vida 63 201907
    A Vida 63 201908
    A Vida 63 201909
    A Vida 63 201910
    A Vida 63 201911
    A Vida 63 201912

    Sample of the table needed: (base of second entry of both tables, sample data)
    Quarter example

    Inssurance Type Accrual StartDate
    A Vida 90 201903
    A Vida 90 201906
    A Vida 90 201909
    A Vida 90 201912
    A Vida 75 202003
    A Vida 75 202006
    A Vida 75 202009
    A Vida 75 202012


     Appreciate your help and available to clear any doubt.

    Kind regards

    Paulo



    ------------------------------
    Paulo Margarido
    IT Consult
    ------------------------------

    Attachment(s)

    xlsx
    Sample data.xlsx   20 KB 1 version