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

Hello TOMALLAN,

I take advantage of this small moment of satisfaction to inform you that thanks to your work we have success a sale.

Thank you to you.

[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>

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

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

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]

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

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.

[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

 

 

[Author]

Posts