Cumulative sum

[GAETAN GARINParticipant Started: 1Replies: 8Total: 9]

Hello I have to realize a state of type(chap) income statement(profit and loss account) in accounting.
I look for a function which can allow me to make an analysis according to one beach(range) of date.
This analysis has to allow to make a cumulative sum by taking into account a date and a departure of a date of the end.
Well évidement the column has to make the accumulation of all the sums being situated on this range of date.
Have you a solution to create this range of analysis?

[tomallanKeymaster Started: 0Replies: 417Total: 417]

There are solutions available, which could be as simple as a single DAX function, but may require a multi-function combined into a single formula. Before attempting an answer, let me first ask:

Have you already started a data model in Power Pivot. If not, the first task would be looking at formulating a basic model in Power Pivot, including a “Calendar” table.

Are you using a standard calendar (Jan 1 – Dec 31), a fiscal calendar (Apr – Mar or Jul – Jun or Oct – Sep), or a custom calendar like a 4-4-5?

For this cumulative sum are you trying to cumulate over month-to-date, quarter-to-date, year-to-date or a custom period-to-date or a lifetime-to-date?

Is your source data coming from CSV files or a relational database like SQL Server?

I am familiar with GL reports in accounting. It would help though to know about your tables and relationships in your current Power Pivot model.

[GAETAN GARINParticipant Started: 1Replies: 8Total: 9]

Hello TOMALLAN,

To answer your questions:

I use a connection to a SQL relational database Server.

I use a standard calendar, from 01/01/2014 to 31/12/2014.
I also have to create a column allowing me to isolate months.
Months are to identify in the following way from M01 to M12 Exemple “M01 = in January”.

For the cumulative sum I wish to be able to calculate the cumulative sum between two reference months.
Thus I need a formula which includes that the analysis has to begin as from the month defines as that of the departure and to finish in the month defines as limit.

Thus I wish data analysis which can leave of the 01/01/2014 in 31/05/201 for example, but also data which would begin from 01/03/2014 till 31/08/2014.

I began to draft a request DAX but I do not manage to implement a monthly multi-filter.

Here is my request in Excel:

VALEURCUBE (“ThisWorkbookDataModel”; ” [seen writings with Account reporting]. [CR_Intitule]. [” *A16 and “]”; ” [periods]. [Calendar year]. [” *A11 and “]”; ” [periods]. [Month]. [” *A12 and “]”; ” [ Measures]. [The Somme(Sum,Nap) of signed Amount] “)

The table ” Seen writings with Account reporting ” is a view creates under SQL.
The table Period contains my calendar(timetable).
I have to create two filters on cells(units) Excel:
The first one over the year, the second over the month.
The measure resumes(takes back) the sum of the signed amounts (+) (-)

Solution 1:

In this formula the user will have to inform in a cell(unit) Excel months to be analyzed.</span>
This solution is a little bit boring because the user is to oblige to inform the periods manually.

Solution 2:

I also thought of using a segment in the board which could be used in the analysis to define the range of date to be analyzed.
I confront even there with two difficulties:

1) How to insert a segment without dynamic crossed board?
2) How to use this segment to define the range of date to be analyzed in the formula DAX.

I admit that the second solution (use of a segment) shall facilitate me largely the task.

Thank you for your help

 

 

[GAETAN GARINParticipant Started: 1Replies: 8Total: 9]

I forgot to specify that I created one relation between the dates of the table “Period” and those of the table ” Seen writings with Account reporting “.

Besides I found that we can concaténer selection criteria in the DAX on the same variable.

For exemple :

“[Périodes].[Mois].[“&A12&A13&”]”

A12 =M01

A13 = M02

But here in my picture(board) this request sends back an error of type #N/A

I think that it is of for the fact that it concataine period and that thus none datum does not correspond to the criètre.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello Gaetan Garin,

I am trying to express your question in US English, please tell me if I my understanding is correct:

You are working with two tables, “Periods” (a calendar table) and “Seen Writings with Account Reporting” (a data table).

These two tables shared a relationship with their date columns.

The Periods table has a least 3 columns, [Date], [Month] and [Calendar Year].

The “Seen Writings with Account Reporting” has at least two columns, which I will also call [Date] and [Signed Amount]. This table also has a measure (calculated field), which is the [Sum of Signed Amount].

What is think you want is for a user to select a date range in Excel and Power Pivot will return the [Sum of Signed Amount]. If this is true, the solution I will propose is a set of two slicers (I think you call a slicer “segment”). The slicers will contain calendar dates, one date to “Start” and the other date to “End”. First, these slicers will be integrated with a pivot table, then I will help you provide a solution using a cube formula (ValeurCube).

When you let me know my understanding is correct, I will proceed with the solution. From how you expressed your cube formula, you are using Excel 2013. I am planning to submit a copy of my workbook as a demo; hopefully it will function correctly in your work environment.

[GAETAN GARINParticipant Started: 1Replies: 8Total: 9]

It is exact.
To allow you to understand better the board I send it to you.

I am on Excel 2010 64 bits but you can open it with Excel 2013.

The zones which I use to update my board are:

Year of analysis</span>
Month of the beginning of analysis</span>
Titles like VENTES FRANCE / SALES FRANCE (first object)

I would want to use the zone “Month of the end of analysis”
To define the maximal border of analysis.

We can actually leave on a seizure of the page of different date by recording complete dates.

If I understood well, the solution which you propose suits me perfectly.

Thank you for your help

[GAETAN GARINParticipant Started: 1Replies: 8Total: 9]

The file is too voluminous for the forum.

I can put me to you has arrangement on a cloud (www.hubic.com) but I need your e-mail address to share you the link of download.

I communicate you mine [email protected]

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello Gaetan,

I sent you an email with an attached modified dashboard.

If you have difficulty with capturing the amounts for the range of months, the next step would be to create a new measure designed to responds to actual start and end dates. This would mean the date range logic would be built into the measure in Power Pivot, not in the cube formulas (your cube formulas will be simpler as a result).

Let me know if you have any questions on the changes I made, and when you are ready to take the next step.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello Gaetan,

I am glad you received my email with attached dashboard. I am pleased also you noticed how the slicers (segments) worked with disconnected tables to manipulate critical cells in the workbook.

The next step will be to evaluate how to create a new measure that responds, within the Power Pivot window, to the slicers for the selected date range.

[GAETAN GARINParticipant Started: 1Replies: 8Total: 9]

Hello Tom Allan,

I am very satisfied that you found me a solution to my problem.
One thank you for your work.
Everything works perfectly it is brilliant.
I think of having understood(included) the formula of calculation ” Report the Somme of signed Amount “.

I have however some question has to rest(pose) you to understand(include) better the structure of the requests and their uses.

In the formula of the ” Report the Somme of signed Amount ” you have you use the function HASONEVALUE followed by the criterion 1 then put &&.
I do not know this function (&&) it does it correspond has a function “And”?

In tables ” Start Month ” and “End Month” you created a measure.
Start Month TOPN (1; ‘ End Month ‘ End Month ‘ [Month]; 1)
End Month TOPN (1; ‘ End Month ‘ End Month ‘ [Month]; 0)

I do not master well this function
I understood that she allows to send back a number given by lines down from after a specified expression.

Has what corresponds “N_Value” in the formula?
The last character of the formula corresponds to the order.
I did not understand why in the formula of Start Month this criterion is 1 and in that of End Month it is 0?

I held has to thank you once again for your help.

[GAETAN GARINParticipant Started: 1Replies: 8Total: 9]

I did not understand to what corresponds the function(office) “&LC (-3)&” in the formula

=SIERREUR (VALEURCUBE (“ThisWorkbookDataModel”; ” [seen writings with Account reporting]. [ CR_Intitule]. [” &LC (3)&”]”; ” [Measures]. [Adjournment(transfer) the Somme(Sum,Nap) of signed Amount] “; Slicer_Année_Civile; Slicer_Mois; Slicer_Mois1); 0)

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Regarding && you are correct, it means AND. I use it instead of AND not only because it is shorter, but because the AND function is limited to 2 conditions and the && is not. Technically AND is a function and && is an operator.

TOPN can be used for text like MIN and MAX are used for numbers. MINA and MAXA, meant for text, did not give me the results TOPN is different from MIN and MAX because MIN and MAX always return single values, but TOPN returns a table. The 1 (the N value) at the beginning of TOPN means I only want one row in my result (which will be either the “top 1” or the “bottom 1”. The last parameter, 1 or 0, refers to the sort order, 0 is for descending order, 1 is for ascending order.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Gaetan:

I searched a copy of the workbook sent to you for a reference that would include:
LC (-3
but could find nothing. It does not look familiar at all.

Can you tell me what cell has the formula?

I am going to send you a revision of the dashboard. When I first checked for references to cells B8 and B9, I only found them in cells D13 to D22, so those were the only calculations I worked with.

I noticed may other formulas further down in column D that had a used a value of:
#REF!
in the formula and used the original measure. The new revision updates formulas in the other cells found later in column D with the modified measure and references to the slicers.

If the new measure does not apply to cells below D22, feel free to discard the Revision 02 that I will send you via email now.

Your questions are good, I am happy to answer them.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello Gaetan,

In case you were wondering how I created the disconnected tables for Report Year, Start Month and Start Year, the SQL statements follow (I created a table in concept similar to your Periods table):

For Report Year:

select distinct [Année Civile] from Périodes order by [Année Civile]

For Start Month and End Month, the SQL statement is the same:

select distinct Mois from Périodes order by Mois

[GAETAN GARINParticipant Started: 1Replies: 8Total: 9]

<span class=”transpan”><span id=”tran0″>Hello Tom Allan,</span>

<span id=”tran1″>Thank you for these elements of answers.</span>

<span id=”tran2″>It allowed me to understand better your formulae.</span>

<span id=”tran3″>Thanks to your work I am going to be able to move forward on the conception of my dashboard.</span>

<span id=”tran4″>Thank you very much</span></span>

[Author]

Posts