Reverse Cumulative measure

[Atifz]

Hi all,

I am trying to create a reverse cumulative measure and I am having difficulty when there are blanks in my data. 

Using this calculated measure, I get the table below, but where there are blanks, the value comes out as the total.

Cumulative =

CALCULATE (

'Registrations'[Registrations],

FILTER(

ALL( 'Registrations'[DaysDiff]),

                'Registrations'[DaysDiff] >=MAX('Registrations'[DaysDiff])))

DaysDiff	Registrations	Cumulative
0	134	460
1	85	326
2	49	241
3	47	192
4	34	145
5	27	111
6	11	84
7	17	73
8	17	56
9	11	39
10	13	28
11	3	15
12	1	12
13		460
14		460
15	3	11
16	2	8
17	2	6
18		460
19		460
20	1	4
21		460
22		460
23		460
24	3	3

Can anyone help me modify the calculation, so on the days where there are blank registrations, the cumulative column just takes the value above. I don't want to get rid of the blank rows, as I need all the daysdiff there.

The model I am using can only create calculated measures and not columns.

The final table should look like the below:

DaysDiff	Registrations	Cumulative
0	134	460
1	85	326
2	49	241
3	47	192
4	34	145
5	27	111
6	11	84
7	17	73
8	17	56
9	11	39
10	13	28
11	3	15
12	1	12
13		12
14		12
15	3	11
16	2	8
17	2	6
18		6
19		6
20	1	4
21		4
22		4
23		4
24	3	3

Thanks

[amitchandak]

Check

reverse cumm = 
var _cumm =CALCULATE (
sum('Registrations'[Registrations]),
FILTER(
ALL( 'Registrations'[DaysDiff]),
                'Registrations'[DaysDiff] <=MAXX(all('Registrations'),'Registrations'[DaysDiff])))
var _ear =CALCULATE (
sum('Registrations'[Registrations]),
FILTER(
ALL( 'Registrations'[DaysDiff]),
                'Registrations'[DaysDiff] <=MAX('Registrations'[DaysDiff])-1))
return
_cumm -_ear

There is minor diff, please check

Solution link :https://www.dropbox.com/s/dvrkkmupwk4r7fz/ReverseCumm.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Atifz]

hi @amitchandak,

I get an error at this part in in both of the rows.

sum('Registrations'[Registrations])

If it helps, [Registrations] is a measure made from CountA([BidderID]), where BidderID is a string.

Thanks

[amitchandak]

Try using CountA([BidderID]) or [Registrations], in place of Registrations[Registrations]

[Atifz]

hi @amitchandak 

It doesn't let me put the CountA in the Sum, but if I replace the whole row with it, I get the same results as the beginning, with the rows with blank showing 460

[amitchandak]

Share sample of data in your original data format

[Atifz]

hi @amitchandak ,

My data model is from analysis services. How would I be able to share sample data?

[v-frfei-msft]

Hi @Atifz ,

Export to excel removing any Confidential Information and share your DAX formuals here. Please upload your files to One Drive and share the link here.

[parry2k]

@Atifz try changing following

  MAX('Registrations'[DaysDiff] <= 'Registrations'[DaysDiff]

 Would appreciate Kudos 🙂 if my solution helped.

[Atifz]

Hi @parry2k ,

Sorry, I am quite new to Power BI. Could you please clarify what I need to change and what the full measure should be.

Thanks