Running total until Today

[Anonymous]

Hi guys,

Below is the link for a sample data set where I'm trying to create a running total for actuals until current week where as I cannot seem to find a way to achieve this. The earlier posts have a date field to have this achieved but I have a column with week & year column to have this achieved, not sure how to have this achieved?

Please help.

https://1drv.ms/u/s!Aj7kf6Fnqc5SwwbUodJXttTxGA-A

Regards,

A!

[Johanno]

Maybe this can help in any way:

1. Goto Edit Queries and sort by date

2. Create in index column

3. Create a calculated column by:

Cumulative = CALCULATE(SUM(Data[Actual]);FILTER(Data;Data[Index]<EARLIER(Data[Index])-1))

But I see you have many rows without dates which makes me confused.

[Anonymous]

Hi @Johanno,

Thank you for the below alternative but this one gives the 3.5million as actuals in value which should not be the case and I think this is because of the number of blanks i have in my dataset. The sample I shared is subset of data 😞

Regards,

A!

[Johanno]

Ok, I don't fully understand. The sum of all actuals should be the last figure in the cumulative column.

[Anonymous]

Hi @Johanno,

I want a cumulative value of actuals until the current week. However, the calculation provides the overall total value but does not breakdown per week value when plotted in line visual. I used the below mentioned measure to list me the values per week but gives me the actuals until the max value of date but I want the calculation to stop for the current week. 

Measure: Actuals show up for the entire time period when I use the below calculation where column [Yr | Wk - DueDate] is a combination of year and week #

CALCULATE(
SUM('tblEBV'[ACTUAL BV]),
FILTER(
ALLSELECTED('tblEBV'[Yr | Wk - DueDate]),
ISONORAFTER('tblEBV'[Yr | Wk - DueDate], MIN('tblEBV'[Yr | Wk - DueDate]), DESC)
)
)

Kind regards,

A!

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Anonymous]

Hi @Greg_Deckler,

Thank you for sharing the link, I had already made use of the logic earlier in my dataset but does not seem to work for me as I have a derivative column for year and week based on date column to plot in my line diagram and the actuals flow beyond the current week which I want to restrict 😞

Kind regards,

A!