Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Problem with Cumulative Sum

After  jdbuchanan71 helped me with this one (https://community.powerbi.com/t5/Desktop/Evolution-Cumulative-Sum/m-p/757213#M364895 ), a new problem has come up:

 

I have a running total calculation in order to show me the Evolution of notes that have been closed in a certain period of time. Whenever there aren't notes closed in a certain week my Evolution calculation is wrong.
I have tried performing something like:

#Evolution = 
var CurrentWeek = MAX(tab_Notas[Semana Data Encerramento])
var LastWeek = CurrentWeek - 1

return

IF([#Notas encerradas] > 0;
	CALCULATE(    //TRUE
	[#Notas encerradas];
	ALL(tab_Notas[Semana Data Encerramento]);
	tab_Notas[Semana Data Encerramento] <= CurrentWeek
);
	CALCULATE(    //FALSE
	[#Notas encerradas];
	ALL(tab_Notas[Semana Data Encerramento]);
	tab_Notas[Semana Data Encerramento] <= LastWeek
)
)

but can't seem to get it working properly.

The measures goes as follows:
[.Total de Notas] is a COUNTROWS of the table;
[#Notas encerradas] shows me the amount of notes that have been closed and is a CALCULATE including the filters I need;

[#RT Week] is a CALCULATE as answered by jdbuchanan71 in my other question
[#Evolution] is simply ===> [.Total de Notas] - [#RT Week]

 

Any suggestions?

 

Regards,
Thiago Izidoro.

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Got it a few days ago:

     

    Created two measures:

    First one:

     

    #Notes closed in the past week = 
var currentweek = MAX(tab_Notas[Closing Week])
var lastweek = currentweek - 1
return
    CALCULATE(
        [#Notas encerradas];
        tab_Notas[Closing Week] <= lastweek
    )

    And the second one is what I was looking for:

     

    #Evolution =
var currentweek = MAX(tab_Notas[Closing Week])
var lastweek = lastweek - 1
return
    IF([#Notas encerradas] > 0;
        [.Total de Notas] - [.#RT Week];   //TRUE
        CALCULATE(                              //FALSE
        [.Total de Notas] - [#Notes closed in the past week];
        ALL(tab_Notas[Closing Week]);
        tab_Notas[Closing Week] <= lastweek
        )
    )

     

     

    Regards,

    Thiago Izidoro.

3 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    7 years ago

    Anonymous 

     

    It seems that you may use the code below.

    RETURN
    [.Total de Notas]
        - CALCULATE (
            [#Notas encerradas],
            tab_Notas[Semana Data Encerramento] <= CurrentWeek
        )
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks v-chuncz-msft , but it still doesn't work. It returns the same as posted above.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Got it a few days ago:

       

      Created two measures:

      First one:

       

      #Notes closed in the past week = 
var currentweek = MAX(tab_Notas[Closing Week])
var lastweek = currentweek - 1
return
    CALCULATE(
        [#Notas encerradas];
        tab_Notas[Closing Week] <= lastweek
    )

      And the second one is what I was looking for:

       

      #Evolution =
var currentweek = MAX(tab_Notas[Closing Week])
var lastweek = lastweek - 1
return
    IF([#Notas encerradas] > 0;
        [.Total de Notas] - [.#RT Week];   //TRUE
        CALCULATE(                              //FALSE
        [.Total de Notas] - [#Notes closed in the past week];
        ALL(tab_Notas[Closing Week]);
        tab_Notas[Closing Week] <= lastweek
        )
    )

       

       

      Regards,

      Thiago Izidoro.