Help needed in getting rolling numbers for dates

[Anonymous]

Hi Team,

I'm seeking some help in achieving the below scenario in PowerBI which we can easily get using Excel formula. 

I want to get the total no. of applications for each date ("Tme" column could be any date) calculated from the before date in the column.

I was thinking it is easy but I'm not getting the trick/logic to apply.

Can anyone help..?

Thanks in advance.

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

According to your description, what I understand is that only the first row of the application count column has a value, and the values of the remaining rows are calculated by the formula: A-B+C.

If it is what I think it is, please see my solution.

Sample data:

If you want to get the same results as in Excel, we can create the following metasure:

Measure =
CALCULATE(
    SUM('Table'[Applications count]),
    FILTER(ALL('Table'),[Time]<=MAX('Table'[Time]))
)
-
CALCULATE(
    SUM('Table'[Deprictae]),
    FILTER(ALL('Table'),[Time]<MAX('Table'[Time]))
)
+
CALCULATE(
    SUM('Table'[New Add]),
    FILTER(ALL('Table'),[Time]<MAX('Table'[Time]))
)

Or we can achieve the same result by creating calculated columns.

What I did is to calculate the first value of Applications count column with the row-by-row accumulation of the remaining two columns

I created 3 columns to be used as auxiliary calculations, the DAX is as follows:

Column = CALCULATE(MAX('Table'[Applications count]),FILTER('Table',[Time]<=EARLIER('Table'[Time])))

Column 2 = CALCULATE(SUM('Table'[Deprictae]),FILTER('Table',[Time]<EARLIER('Table'[Time])))

Column 3 = CALCULATE(SUM('Table'[New Add]),FILTER('Table',[Time]<EARLIER('Table'[Time])))

So the expression of the final result is very simple:

Result = [Column]-[Column 2]+[Column 3]

Result:

Please check the sample pbix file's link down below

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks, @v-angzheng-msft 
This is what I'm looking at.

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

I created the sample and please check whether it suits your business case.

Result =
VAR currentdate =
MAX ( 'Table'[Date] )
RETURN
CALCULATE (
LASTNONBLANKVALUE (
'Table'[Date],
SUMX (
'Table',
'Table'[Applications Count] - 'Table'[Deprictae] + 'Table'[New Add]
)
),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] < currentdate )
)

https://www.dropbox.com/s/ovb5eql4n4uvf92/gurunadhs.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.