Forum Discussion

Isildur13's avatar
Isildur13
Frequent Visitor
1 year ago
Solved

Create running total based on expected daily average

Hi Community!

I have tried to wrap my head around how to create a Matrix visual with a measure calculating a form of expected delivered value like a running total.

 

I have 2 tables; Datetable, contractstable

 

In my contractstable I have data like below:

contractnumberstartdateenddatetotalamountcontractdays
XYZ01-01-202431-12-2024100.000365

 

I want to know the daily expected amount and then create a running total up until todays date in order to know how much amount I should have delivered in order to reach full amount when the contract end.

Need Help
  • Selva-Salimi's avatar
    Selva-Salimi
    1 year ago

    Hi Isildur13 

     

    your rules are not clear, but lets assume that you expect to deliver same amout on every days of your contract. then you can write a measure as follows:

     

    Measure =
    var _totaldays = DATEDIFF(max('Table'[startdate]) , max('Table'[enddate]),DAY)
    var _passeddays = DATEDIFF(MAX('Table'[startdate]),today(),DAY)
    return
    sum('Table'[totalamount])*_passeddays /_totaldays
     
    If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.

5 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    Isildur13 

    DailyExpectedAmount = 
    DIVIDE(Contractstable[TotalAmount], Contractstable[ContractDays])
    RunningTotalAmount = 
    CALCULATE(
    SUMX(Contractstable, [DailyExpectedAmount]),
    FILTER(
    ALL(DateTable), 
    DateTable[Date] <= MIN(TODAY(), MAX(DateTable[Date]))
    )
    )

    💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

  • Selva-Salimi's avatar
    Selva-Salimi
    Solution Sage
    1 year ago

    Hi Isildur13 

     

    your rules are not clear, but lets assume that you expect to deliver same amout on every days of your contract. then you can write a measure as follows:

     

    Measure =
    var _totaldays = DATEDIFF(max('Table'[startdate]) , max('Table'[enddate]),DAY)
    var _passeddays = DATEDIFF(MAX('Table'[startdate]),today(),DAY)
    return
    sum('Table'[totalamount])*_passeddays /_totaldays
     
    If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.
    • Isildur13's avatar
      Isildur13
      Frequent Visitor
      1 year ago

      Hi Selva

       

      Thank you very much. Since the business requirements changed a bit your solution worked as they expected 🙂

  • Thejeswar's avatar
    Thejeswar
    Super User
    1 year ago

    Isildur13 ,

    I took a different approach to handle it from Power Query. The Below are the steps I followed

    1. Merged the date column from the date table to the contracts table based on year. The Assumption is date table has all the dates in the year.

    2. Created a new column for dailyamount using the formula

     

    dailyamount = [totalamount]/[contractdays]

     

    3. Created a new column to determine the days that are part of the running total to limit to current date

     

    validdays = if [Date] <= Date.From(DateTime.LocalNow()) then 1 else 0

     

     

    The Final contracts table will looks like the one below

    4. Now create a new measure with the below formula

     

    runningtotal = CALCULATE(SUM('ContractsTable'[dailyamount]), FILTER(ALL('ContractsTable'),'ContractsTable'[Date] <= MAX('ContractsTable'[Date])))

     

    The Final Visual will look like shown below

     

     

    Regards,

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi,

      Thanks for the solutions Thejeswar , Selva-Salimi  and Kedar_Pande  offered, and i want to offer some more infotmation for user to refer to.

      hello Isildur13 , based on your description, you can refer to the following solution.

      Sample data 

      Calendar table

      Relationship:date table with data table: 1:n

      Create a average_amount measure.

      average_amount =
CALCULATE (
    DIVIDE ( SUM ( contracts[totalamount] ), SUM ( contracts[contractdays] ) ),
    CROSSFILTER ( 'Date'[Date], contracts[startdate], NONE )
)

      Then create a new measure

      Sumtotal =
VAR a =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        ALLSELECTED ( 'Date' ),
        'Date'[Date] >= MIN ( contracts[startdate] ),
        'Date'[Date] <= MIN ( MAX ( 'Date'[Date] ), TODAY () )
    )
RETURN
    IF ( MAX ( 'Date'[Date] ) <= TODAY (), a * [average_amount] )

      Output

      Best Regards!

      Yolo Zhu

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