Forum Discussion

Fali324's avatar
Fali324
Icon for Helper II rankHelper II
1 year ago
Solved

Cash flow Dashboard

Hi,

 

I need some help as I'm stumped at this point.

 

I have the following table in power bi:

Plot NoTotal Plot £PhaseDesign Start DateDesign End DateDesign DurationConstruction Start DateConstruction End DateConstuction DurationTotal DurationWeekly rateDesign SpendConstruction Spend
Plot 1550000Phase 101/01/202526/02/2025827/02/202515/05/2025111928947231579318421
Plot 10663068Phase 218/06/202813/08/2028801/01/202526/03/2025122033153265227397841
Plot 11667746Phase 327/03/202515/05/2025716/05/202518/07/202591641734292139375607
Plot 12672424Phase 319/07/202530/08/2025631/08/202519/10/202571351725310350362074
Plot 13677102Phase 320/10/202501/12/2025602/12/202520/01/202671352085312509364593




What I would like to do is create a table where the months are column headings and the rows are plots showing the spend for that month.

How can I do that?


Need Help
  • danextian's avatar
    danextian
    1 year ago

    Your expected result is a bit confusing. For Plot1, both January and February 2025 have 4 weeks of Design, so their values should be the same but your result shows otherwise. That aside, assuming the weekly spend should be plotted at the end of each week starting from the Design or Construction start date (e.g., a Design start of Jan 1, 2025 would place the first point on Jan 7), you can try the following formula.
    Note: Dates refers to a disconnected calendar table that spans from the earliest to the latest of all relevant date columns.

    Weekly Design Spend = 
VAR SummaryTable =
    SUMMARIZE (
        Data,
        Data[Plot No],
        Data[Phase],
        Data[Design Start Date],
        Data[Weekly rate],
        Data[Design Duration]
    )
VAR _GeneratedTable =
    GENERATE (
        SummaryTable,
        ADDCOLUMNS (
            GENERATESERIES ( 1, [Design Duration], 1 ),
            "@End of Week",
                [Design Start Date] + ( [Value] * 7 ) - 1
        )
    )
VAR _FilteredTable =
    FILTER ( _GeneratedTable, [@End of Week] IN VALUES ( Dates[Date] ) )
RETURN
    SUMX ( _FilteredTable, [Weekly rate] )

     

    Please see the attached pbix.

9 Replies

    • Fali324's avatar
      Fali324
      Icon for Helper II rankHelper II
      1 year ago

      Hi, As an example I've used Plot 1 and Plot 10.

      I need the table to become this:

       

      Plot NoCost TypeDateSpend
      Plot 1Design01/01/202528947.37
      Plot 1Design08/01/202528947.37
      Plot 1Design15/01/202528947.37
      Plot 1Design22/01/202528947.37
      Plot 1Design29/01/202528947.37
      Plot 1Design05/02/202528947.37
      Plot 1Design12/02/202528947.37
      Plot 1Design19/02/202528947.37
      Plot 1Design26/02/202528947.37
      Plot 1Construction05/03/202528947.37
      Plot 1Construction12/03/202528947.37
      Plot 1Construction19/03/202528947.37
      Plot 1Construction26/03/202528947.37
      Plot 1Construction02/04/202528947.37
      Plot 1Construction09/04/202528947.37
      Plot 1Construction16/04/202528947.37
      Plot 1Construction23/04/202528947.37
      Plot 1Construction30/04/202528947.37
      Plot 1Construction07/05/202528947.37
      Plot 10Design18/06/202833153.4
      Plot 10Design25/06/202833153.4
      Plot 10Design02/07/202833153.4
      Plot 10Design09/07/202833153.4
      Plot 10Design16/07/202833153.4
      Plot 10Design23/07/202833153.4
      Plot 10Design30/07/202833153.4
      Plot 10Design06/08/202833153.4
      Plot 10Design13/08/202833153.4
      Plot 10Construction01/01/202533153.4
      Plot 10Construction08/01/202533153.4
      Plot 10Construction15/01/202533153.4
      Plot 10Construction22/01/202533153.4
      Plot 10Construction29/01/202533153.4
      Plot 10Construction05/02/202533153.4
      Plot 10Construction12/02/202533153.4
      Plot 10Construction19/02/202533153.4
      Plot 10Construction26/02/202533153.4
      Plot 10Construction05/03/202533153.4
      Plot 10Construction12/03/202533153.4
      Plot 10Construction19/03/202533153.4
      Plot 10Construction26/03/202533153.4
      • Fali324's avatar
        Fali324
        Icon for Helper II rankHelper II
        1 year ago

        and the visuals I want to create are:

        so the weeks inbetween the start and end date need to be automatically generated. 

  • grazitti_sapna's avatar
    grazitti_sapna
    Icon for Super User rankSuper User
    1 year ago

    Hi Fali324 

    1. Unpivot to Normalize the Date Ranges

    • You'll need to transform the date ranges (Design Start to End, Construction Start to End) into individual rows per month.

     

    • This is often done with a date table and a many-to-many relationship or via a generated table where each plot has a row for each month it spans.


    2. Allocate Spend Per Month

    • Calculate the number of months between the start and end dates of each phase.
    • Divide the total cost proportionally across those months (e.g., Total Plot £ / number of months).
    • Assign the corresponding spend to each month.


    3. Pivot the Data

    • Once each plot has a row for each month and spend value, pivot the month column so that:
    • Rows = Plot No
    • Columns = Month-Year (e.g., Jan-2025, Feb-2025)
    • Values = Monthly Spend




     I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
     Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
     As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
     Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!

  • v-achippa's avatar
    v-achippa
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi Fali324,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    Thank you danextian for the prompt response.

     

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user resolved your issue? or let us know if you need any further assistance.

    Thanks and regards,
    Anjan Kumar Chippa