Forum Discussion

nicoenz's avatar
nicoenz
Icon for Helper III rankHelper III
2 years ago
Solved

Running total values and value for future periods

Hi everyone,

i have a table that contains Volume data for December and January. As of January 7, there's no more data

 

The running total measure that i use:

Running total =   var Turro = CALCULATE(SUM('Table'[Volume]),
                            filter(ALL('Table'),'Table'[Fiscal year/period] = MAX('Table'[Fiscal year/period])),
                            filter(ALL('Table'),'Table'[WorkDayPeriod] <= MAX('Table'[WorkDayPeriod])))
                           
                            return
                            If(Turro <>0,
                                Turro,
                                Turro+ CALCULATE(SUM('Table'[Volume]),REMOVEFILTERS('Table'[WorkDayPeriod]))
                                )

gives the following table:

 

Since there are no future data after January 7 there's no value at all but I would like to have a flat line from day 7 till 22 with the last value.
 

 

 

How can I make it?

 

Regards and happy 2024!

Need Help
  • nicoenz's avatar
    nicoenz
    2 years ago

    I couldn't make it work because future dates were not included so the measures didn't know what to do. I solved it by adding a manual table with future dates, appending this new table with my FactTable, adjusting the measures and voila!!!

    i guess it's not the best and tidiest way of doing it but it works so far

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi nicoenz ,

    Thanks for reaching out to us with your problem. Base on your description, it seems that you want to get the running total for the future dates. You can follow the steps to get it:

    1. Create a separated date dimension table(DO NOT create any relationship with your fact table)

    2. Apply the date field of date dimension table on X-axis of your line chart

    3. Update the formula of measure [Running total] as below

    Running total =
VAR Turro =
    CALCULATE (
        SUM ( 'Table'[Volume] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Fiscal year/period] = SELECTEDVALUE ( 'Date'[Date] )
        ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[WorkDayPeriod] <= SELECTEDVALUE ( 'Date'[Date] )
        )
    )
RETURN
    IF (
        Turro <> 0,
        Turro,
        Turro
            + CALCULATE ( SUM ( 'Table'[Volume] ), REMOVEFILTERS ( 'Table'[WorkDayPeriod] ) )
    )

    If the above one can't help you get the expected result, please provide some raw data in your table (exclude sensitive data) with Text format, your line chart Fields settings and your expected result with special examples and screenshots. It would be helpful to find out the solution. You can refer the following link to share the required info:

    How to provide sample data in the Power BI Forum

     

    And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

    • nicoenz's avatar
      nicoenz
      Icon for Helper III rankHelper III
      2 years ago

      Hi Rena, i tried and couldn't make it work. tried tweaking it a bit to no avail. 

      I will upload the file with some more explanations

      thanks!!!!