Cumulative Sales only by Selected Year

[bajimmy1983]

Hi all,

I am using power pivot with all relationships created and OK. There is one Dim Table called "date" and another Transaction Table called "pipe".

In "date" table there is a column with full date (DD/MM/YY) and another derived from it called "Year;

In "pipe" table I have same columns;

Relationship was created between Date columns from "date" table and Date column from "pipe" table.  

My objective is to set up a Calculated Filed (Measure) called CUMULATIVE ACTUAL BRL that will bring RUNNING TOTAL SALES month by month, but just from the year selected in the pivot table (FILTERS area in Pivot) and not all years <= this year.

In case my explanation is not clear... "pipe" table has sales from 2015 up to April, 2017, but I want to have a calculated field that will bring cumulative sales according to selected year PivotTable filters.

PivotTable Filter = 2017		Desired Outcome
Month Name	ACT. BIL. BRL	CUMULATIVE ACT. BRL
JAN	740.085	740.085
FEV	507.942	1.248.027
MAR	2.708.707	3.956.734
ABR	2.489.969	6.446.703
MAI	2.269.615	8.716.318
JUN	3.017.910	11.734.228
JUL	3.300.621	15.034.849
AGO	2.242.945	17.277.794
SET	1.701.142	18.978.936
OUT	1.971.313	20.950.249
NOV	1.704.314	22.654.563
DEZ	1.222.447	23.877.010
Grand Total	23.877.010	23.877.010

Thank you so much in advance,

[MattAllington]

You need time intelligence. Everything you need to know is in this article I wrote

http://exceleratorbi.com.au/dax-time-intelligence-beginners/

[bajimmy1983]

Hi Matt, thanks for your reply. 

Actually I saw your article last week, but I forgot. Sorry. Thanks for remembering me. 

So, I start with this Calculated Field: 

CALCULATE([ACT. BIL. BRL];FILTER(ALL('date');'date'[Year] = MAX('date'[Year]) && 'date'[Date] <= MAX('date'[Date]))

I ended up with this result:

Year	2017
MONTH	ACT. BIL. BRL	TOT YTD ACT. BIL. BRL
JAN	2224748	2224748
FEV	1370940	3595688
MAR	0	3595688
ABR	0	3595688
MAI	0	3595688
JUN	0	3595688
JUL	0	3595688
AGO	0	3595688
SET	0	3595688
OUT	0	3595688
NOV	0	3595688
DEZ	0	3595688
Grand Total	3595688	3595688

I found another good article about "Cumulative Total" (http://www.daxpatterns.com/cumulative-total/). Here Marco Russo also give an example on how to "... avoid the propagation of the last value in periods that are later than the last transactions in your data."

I tried this Measure, but fail:

TOT YTD ACT. BIL. BRL :=
IF (
    MIN ( 'date'[Date] )
        < CALCULATE ( MAX ( pipe[MES CONTAB BILLING LOCAL] ); ALL ( pipe ) );
    CALCULATE (
        [ACT. BIL. BRL];
        FILTER (
            ALL ( 'date' );
            'date'[Year] = MAX ( 'date'[Year] )
                && 'date'[Date] <= MAX ( 'date'[Date] )
        )
    )
)

Year	2017
MONTH	ACT. BIL. BRL	TOT YTD ACT. BIL. BRL
JAN	2.224.748	2.224.748
FEV	1.370.940	3.595.688
MAR	0	3.595.688
ABR	0	0
MAI	0	0
JUN	0	0
JUL	0	0
AGO	0	0
SET	0	0
OUT	0	0
NOV	0	0
DEZ	0	0
Grand Total	3.595.688	3.595.688

[bajimmy1983 · Accepted solution]

Hi all.

I think I found a solution regarding to my last reply. Talking to my wife today about it (and Yeah, she does not know anything about DAX) she helped me to figure out a way using a different approach of IF statement. Funny! At least it is working.

I know that you may have a lot of other ways to reach same outcome and I kindly ask you to share with us. Regardless this issue is a very basic one I think it is important to know about other ways and of course I am learning a lot with Power BI Community.

My New Calculated Field: 

ACT. Y. BIL. BRL :=
IF (
    OR (
        CALCULATE ( [ACT. BIL. BRL]; FILTER ( 'date'; 'date'[Month Number] ) ) = 0;
        CALCULATE ( [ACT. BIL. BRL]; FILTER ( 'date'; 'date'[Month Number] ) )
            = BLANK ()
    );
    BLANK ();
    CALCULATE (
        [ACT. BIL. BRL];
        FILTER (
            ALL ( 'date' );
            'date'[Year] = MAX ( 'date'[Year] )
                && 'date'[Date] <= MAX ( 'date'[Date] )
        )
    )
)

Thanks again,

[CharlesK]

You are a life saver. 

Kudos.

[bajimmy1983]

I forgot to place final result: 

[v-huizhn-msft]

Hi @bajimmy1983,

I am very glad you have resolved your issue. Please mark right replay as answer, so other people will find solution easily.

In addtion, in the article, the date comes from Date table, so you'd better use the month in Date table, and the condition is  less than and equals to(<=highlighted in red line). You can modify your statement and row level, and check if it works fine.



Best Regards,
Angelia

[bajimmy1983]

Hi Angelia, thanks for your reply.

Please check if I did correct, because if so, you can see "propagation" issue came back.

I do not want to have last cumulative Billings (7.615.365) replicated in months there is not billings yet. 

Note that in PivotTable I am using a field from 'date'Table called MONTH (which is the name of month. So a string). I tried to use MONTH in the calculated field, but as you already know I got an error because MIN function cannot handle strings, right?

ACT. Y. BIL. BRL TEST :=
IF (
    MIN ( 'date'[Month Number] )
        <= CALCULATE ( MAX ( pipe[MES CONTAB BILLING LOCAL] ); ALL ( pipe ) );
    CALCULATE (
        [ACT. BIL. BRL];
        FILTER (
            ALL ( 'date' );
            'date'[Year] = MAX ( 'date'[Year] )
                && 'date'[Date] <= MAX ( 'date'[Date] )
        )
    )
)

Outcome: 

 Thanks again,