summarize NOT taking into account customers without sales in this year

[Ion_Ander]

Hi,

I have a measure in DAX that tells me the number of customers who have bought less than the previous year.

The problem is that looks like Power Bi not take into account those customers who have not bought this year. But if a client bought the previous year AND not this year, it should also be considered.

this is my DAX code:

DECREASE = 
COUNTROWS(
   FILTER(
    SUMMARIZECOLUMNS(
       EMPRES[NAME],
        CLIENT[CODCLI]
    ,
    "TYTD",   
            CALCULATE( SUM ( CLIENT[IMPORT] ),
            DATESYTD(DimDate2[DateFull]))
,
    "LYTYTD",
            CALCULATE( SUM ( CLIENT[IMPORT] ),
            SAMEPERIODLASTYEAR(DimDate2[DateFull])
            )
),
[TYTD]<[LYTYTD]
)
)

Client is table with the code of the client ([codcli]), the date of the bought (related with the calendar table DimDate2), and the amount ([import]).
In the report i have a slicer with the year, when i select the present year(2021) it says 571 clients meet the condition , but this is not correct.

The strangest thing is that if I try the same formula In DAX Studio it does return the correct number of 934. The difference is that in DAX Studio i put manually the year:

EVALUATE
{
    COUNTROWS (
        FILTER (
            SUMMARIZECOLUMNS (
                EMPRES[NAME],
                CLI[CODCLI],
                "TYTD",
                    CALCULATE (
                        SUM ( CLIENT[IMPORT] ),
                        FILTER ( CLIENT, YEAR ( CLIENT[fecalb] ) = 2021 )
                    ),
                "LYTYTD",
                    CALCULATE (
                        SUM ( CLIENT[IMPORT] ),
                        FILTER ( CLIENT, YEAR ( CLIENT[fecalb] ) = 2020 )
                    )
            ),
            [TYTD] < [LYTYTD]
                && EMPRES[NAME] = "DMI"
        )
    )
}

[v-rzhou-msft · Accepted solution]

Hi @Ion_Ander 

From your statement, I know if you use DATESYTD/SAMEPERIODLASTYEAR function to get TYTD and LYTYTD will get wrong result. However you will get correct result by specific year value.

It is better for you to build a unrelated slicer table and try selectedvalue function in your dax code.

Firstly build a unrelated Year table. And you will build a slicer by year column in it.

DECREASE =
VAR _SELECTYEAR = SELECTEDVALUE(SLICER[Year])
RETURN
COUNTROWS (
        FILTER (
            SUMMARIZECOLUMNS (
                EMPRES[NAME],
                CLI[CODCLI],
                "TYTD",
                    CALCULATE (
                        SUM ( CLIENT[IMPORT] ),
                        FILTER ( CLIENT, YEAR ( CLIENT[fecalb] ) = _SELECTYEAR )
                    ),
                "LYTYTD",
                    CALCULATE (
                        SUM ( CLIENT[IMPORT] ),
                        FILTER ( CLIENT, YEAR ( CLIENT[fecalb] ) = _SELECTYEAR-1 )
                    )
            ),
            [TYTD] < [LYTYTD]
                && EMPRES[NAME] = "DMI"
        )
    )

If this reply still couldn't solve your problem, please share a sample with me by your Onedrive for Business.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Ion_Ander]

Thanks @v-rzhou-msft your solution works fine.......... but now i have a new problem, perhaps you can help.

If i try to put this measure into a matrix or into a chart it give me error, after investigating i see that SUMMARIZECOLUMN  is not compatible with matix and charts.

so, is it possible to do the same with SUMMARIZE and ADDCOLUMNS?  i never use the summarize previously because it is supposed to be discouraged and not very sure about how to use with multiple tables, i supose i need addcolumn....... I´m  a bit lost.

I share my Power Bi where yo can see the error of the matrix:
https://a31069321-my.sharepoint.com/:u:/g/personal/ionander_gabyl_com/Eag3xi-Q20dDk5RQsuoXR8QBv7iA6J...