Running totals over multiple sites

[NZCraig]

Hello all,

Not sure if I'm asking too much of PowerBI/Power query, or if I'm asking too much of myself 🙂

I have a data table that shows a production value total for 4 sites each day. I need to create a graph for each site, showing its production as a running total. Making one graph, and duplicating and filteringto show each site is easy, but any tips on how to get a running total for each site? I have tried many fo the formulas on here, but have hit one that achieves the desired result yet.

My data looks like the below and I have a master date table called 'Date'[ Calendar Date] that has a relationship to the date column in my fact table (i've added in the column I am trying to get at the end) :

SiteId	Date	Value	(Expected Running value)
1	01 July 2021	35	35
2	01 July 2021	20	20
3	01 July 2021	15	15
4	01 July 2021	40	40
1	02 July 2021	10	45
2	02 July 2021	15	35
3	02 July 2021	10	25
4	02 July 2021	12	52

Any help would be very much appreciated!

[Jihwan_Kim · Accepted solution]

Hi,

I tried to create a sample pbix file like the attached.

Please check the below picture and the attached pbix file.

Value measure: = 
SUM( Data[Value] )

Running value per site: = 
IF (
    ISFILTERED ( 'Calendar'[Date] ),
    CALCULATE ( [Value measure:], 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
)

[NZCraig]

Thanks Jihwan_Kim, this one worked a treat. 

[FarhanAhmed]

You can try following 

Running Total = CALCULATE(SUM('Table (2)'[Value]), FILTER(ALLEXCEPT('Table (2)','Table (2)'[SiteId]),'Table (2)'[Date]<=SELECTEDVALUE('Table (2)'[Date])))

[NZCraig]

Thanks FarhanAhmed, I struggled with this one a bit once I tried to get a bit fancier with some other data, but it worked for the intial tables I had.