Power BI Date Comparision

Question

I'm building a report to control the stock of some materials. I have a great list with a large amount of movement data (3,3 million rows)... every movement has its own center (where movement happened), material code, date (when movement happened), type of movement (entrance, consumption, transference...), qty, value, etc. I need to check, for each material and center, when was the last consumption movement happened, compare to the max date from a calendar table, calculate how many days between, and classify in groups (0-30 days, 31-90 days, 91-180 days, etc...).

Is there a way to do this using only measures? I accomplish using tables, but I have a static result. I need to do this in a dynamic way (filtering my Calendar table will adjust the result)...

My fact table has the structure according to the picture below.

Fact table sample

For each [Cen.], [Material], filtered by [TMv] = 201 or 261, return the formula Today() - Max([Dt.lçto])

I would appreciate it if someone could help.

Thanks a lot!!!

Can you provide some sample data and what you've got working using tables? — Alexis Olson
Hi @AlexisOlson, tks for your answer. I made a better description in my question. — Leonardo Amaral Wernke

Leonardo Amaral Wernke Leonardo Amaral Wernke · Accepted Answer · 2020-05-08T13:49:07

After a lot of try and fail, I figured out a way to solve this question, using the following code. The code bellow was used to calculate te last moviment date, according to applied filters.

Último Mov (data) = 

CALCULATE(
    MAXX(
        ADDCOLUMNS(
            SUMMARIZE(
                fSAP_MB51;
                fSAP_MB51[Material]
            );
            "Último Movimento";
                VAR MAXDATE = 
                    MAXX(
                        FILTER(
                            'fSAP_MB51';
                            'fSAP_MB51'[TMv] IN {"201";"221";"241";"261";"281";"543"} &&
                            'fSAP_MB51'[Material] = EARLIER('fSAP_MB51'[Material])
                            );
                            'fSAP_MB51'[Dt.lçto.]
                    )
                VAR MINDATE = 
                    MINX(
                        FILTER(
                            'fSAP_MB51';
                            'fSAP_MB51'[TMv] IN {"101";"102";"861";"862"} && //
                            'fSAP_MB51'[Material] = EARLIER('fSAP_MB51'[Material])
                        );
                        'fSAP_MB51'[Dt.lçto.]
                    )

                RETURN
                CALCULATE(
                    MAX('dCalendar'[Date]) - 
                    SWITCH(
                        TRUE();
                        AND(ISBLANK(MAXDATE);ISBLANK(MINDATE)) ; DATE(2018;03;31);
                        ISBLANK(MAXDATE) ; MINDATE;
                        ISBLANK(MINDATE) ; MAXDATE;
                        MAXDATE
                    )
                );
            "Qtde Estoque";[Qtde Acumulada ALL]
        );
        TODAY() - [Último Movimento]
    );
    FILTER(
        ALLSELECTED('dCalendar'[Date]);
        ISONORAFTER('dCalendar'[Date]; MAX('dCalendar'[Date]); DESC)
    );
    dCalendar[FutureDate] = "Passado"
)

Power BI Date Comparision

1 Answers