cumulative between last month of a previous year and first month of this year

[celantjm]

Hi all,

I have a probleem with the cumulative result between two year.

I want follow the evolution of the stock. In tabel 1, I follow the balance between the in et the out month per month.  I use this formule :

Balance In & Out = CALCULATE(COUNT('Stock incident'[number]); 'Stock incident'[Mouvement] = "IN")-CALCULATE(COUNT('Stock incident'[number]); 'Stock incident'[Mouvement] = "OUT")

The result is good ("number" is the number of the incident).

But I want to know the stock in the end of every month (tabel 2). The result is good in 2018. But, when arrive january 2019, the "forgot" the last month of 2018 and the result. I would like that the result of january 2019 is "1684+2469".

The formule that I use is :

Evolution du nombre d'incidents encore ouverts =

CALCULATE(

    [Balance In & Out];

    FILTER(

        CALCULATETABLE(

            SUMMARIZE(

                'Stock incident';

                'Stock incident'[Date].[NoMois];

                'Stock incident'[Date].[Mois]

            );

            ALLSELECTED('Stock incident')

        );

        ISONORAFTER(

            'Stock incident'[Date].[NoMois]; MAX('Stock incident'[Date].[NoMois]); DESC;

            'Stock incident'[Date].[Mois]; MAX('Stock incident'[Date].[Mois]); DESC

        )

    )

)

Anyone have a solution ?

Thank you

[Anonymous]

Hi.

First of all, you should NEVER, EVER, EVER use the automatically generated date table in the model. I know you do because you use this 'Stock incident'[Date].[NoMois], which is a sign of bad design. Please, do yourself and others a favour and create a proper Date Table in the model together with relationships. You can read about date tables and time intelligence in tabular models on www.sqlbi.com (and about many more things). Once you do have the right setup, please post something that is more readable. I can't help you even if I wanted because I don't understand what you want. I appreciate English is probably not your first language and you're probably using Google Translator but if you don't phrase your question correctly, you won't get a correct answer. Easy as that.

Best

Darek

[celantjm]

Man, you link sucks !

I'm not here to have insult. Insult which is followed with a commercial site ! 

I'm here to have solution. You have a dax-code for my problem, you give. No ? you leave. 

About language, you use one or two approximate word to critic. You have undestand because you can say that my code isn't goed. I know, it's the reason that i'm here. When one of you compatriote will speak in my language with mistake word, i'll say that i don't understand and that I'll not help him. 

[Anonymous]

LoL 🙂

How do you want others to help you if they can't understand the problem? Think clearly, mate, and try to hold your emotions at bay. The "commercial" site - LoL 🙂 - is the best site on DAX you can find as it's led by the two most famous people in the DAX world (but I understand you might not know this).

You seem not to understand my intentions. So be it.

Be well and wait for a solution.

Best

Darek