Running total values and value for future periods

[nicoenz]

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!

[nicoenz]

Hi @v-yiruan-msft and @Ashish_Mathur Ashish

here's the link to the file: https://drive.google.com/file/d/1ynTnYK1FaCR-vPr5h8z6zEBfo_4lyddo/view?usp=drive_link 

Thanks for your help!!!!!!!

[Ashish_Mathur]

Access Denied message.

[nicoenz]

didnt give access to all with the link. sorry

please try again: https://drive.google.com/file/d/1ynTnYK1FaCR-vPr5h8z6zEBfo_4lyddo/view?usp=sharing 

[Ashish_Mathur]

Hi,

1. Create a relationship (Many to One and Single) from the Posting Date column of Table to the Date column of the DateTable.
2. Create the WorkDayPeriod column in the DateTable
3. In the Table visual, drag the WordDayPeriod and Date columns from the DatTable

Make these changes and share the link of the revised PBI file.

[nicoenz]

Hi @Ashish_Mathur ,

done!! it kind of works but the flat segment on the last days is that the initial value level instead of the last value level. See pbix file

https://drive.google.com/file/d/1bKbBZQ3k1Ql1jgDYztiXmRtxOzROD1F_/view?usp=sharing 

thanks again!!!!!

[nicoenz · Accepted solution]

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

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[v-yiruan-msft]

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]

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!!!!