Forum Discussion

arthiannadi06's avatar
arthiannadi06
Regular Visitor
24 days ago
Solved

Dynamic date relationship & Measures for data modelling for a use case

Power BI Data Model Question – Allocating Utility Charges by Service Date I need advice on the best data model for allocating utility charges across calendar months. My utility table has one row pe...
Need Help
  • danextian's avatar
    danextian
    23 days ago

    Hi arthiannadi06 

    I would distribute the bill's amount across its service dates. 

     

    You can split a  single row into multiple rows using Power Query but I'd do this with a small dataset as this can be very slow with a large one.

     

    Alternatively, you can use DAX measures and a disconnected dates table to spread the amount calculate its value for a specific period.

    Please see the attached pbix.

Prince0011's avatar
Prince0011
Solution Sage
24 days ago

This needs a bridge/allocation fact table, not a direct relationship, since one bill row has to spread across multiple months.

Best approach, in Power Query, expand each bill into one row per day (or per month if daily grain is too heavy) within its Read From/Read To range, with a pro-rated amount per row. That table relates cleanly to Calendar[Date], one active relationship, no ambiguity.

For your case, June 15-July 14 becomes 30 rows, each with $3.33/day, June gets 16 of those rows, July gets 14.

Once that's built, your measures are straightforward, standard SUM for Selected Month Total, TOTALYTD for YTD, SAMEPERIODLASTYEAR for % change, and average daily rate is just Amount/Days for the selected period.

 

💡 Helpful? Give a Kudos 👍 — keep the community growing.
✅ Solved your issue? Mark this as the Accepted Solution ✔️
Best regards,
Prince Singh | Data Science & Microsoft Fabric Enthusiast

  • arthiannadi06's avatar
    arthiannadi06
    Regular Visitor
    23 days ago

    Hi Prince,

     

    Thank you for your response. I have used the below DAX

    Allocated daily Charges =
    SUMX(
        'UtilityAppended',
        VAR StartDate = 'UtilityAppended'[read_from]
        VAR EndDate = 'MLGW Appended'[read_to]
        VAR DaysOfService = 'UtilityAppended'[days_of_service]
        VAR DailyRate = DIVIDE('UtilityAppended'[amount], DaysOfService)

        VAR DaysInSelectedPeriod =
            COUNTROWS(
                FILTER(
                    VALUES(Calendar[Date]),
                    Calendar[Date] >= StartDate &&
                    Calendar[Date] <= EndDate
                )
            )

        RETURN
            DailyRate * DaysInSelectedPeriod
    )
     

    I also changed the existing active relationship between the Calendar table and the Utility table to inactive.

    With this approach, do I need to use this new measure for all calculations that rely on the Calendar table in DAX? Will it work correctly with all report filters and slicers?

    Additionally, does having a large number of rows in Power Query impact dashboard performance? If so, are there any best practices I should follow to optimize performance?