Forum Discussion

Atifz's avatar
Atifz
Frequent Visitor
6 years ago

Reverse Cumulative measure

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])))

 

DaysDiffRegistrationsCumulative
0134460
185326
249241
347192
434145
527111
61184
71773
81756
91139
101328
11315
12112
13 460
14 460
15311
1628
1726
18 460
19 460
2014
21 460
22 460
23 460
2433

 

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:

 

DaysDiffRegistrationsCumulative
0134460
185326
249241
347192
434145
527111
61184
71773
81756
91139
101328
11315
12112
13 12
14 12
15311
1628
1726
18 6
19 6
2014
21 4
22 4
23 4
2433

 

Thanks

 
Need Help

9 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Atifz try changing following

     

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

     

     Would appreciate Kudos 🙂 if my solution helped.

    • Atifz's avatar
      Atifz
      Frequent Visitor
      6 years ago

      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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Atifz
      Frequent Visitor
      6 years ago

      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's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

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