Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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:

 

rt_problem.PNG

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.

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

3 REPLIES 3
v-chuncz-msft
Community Support
Community Support

@Anonymous 

 

It seems that you may use the code below.

RETURN
    [.Total de Notas]
        - CALCULATE (
            [#Notas encerradas],
            tab_Notas[Semana Data Encerramento] <= CurrentWeek
        )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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.

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.