cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ion_Ander
Helper I
Helper I

summarize NOT taking into account customers without sales in this year

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"
        )
    )
}

 

 

 

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

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. 

 

View solution in original post

2 REPLIES 2
RicoZhou
Community Support
Community Support

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. 

 

View solution in original post

Thanks @RicoZhou 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... 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors