Stairway to DAX and Power BI - Level 9: Function / Iterator Function Pairs: The DAX MAX() and MAXX() Functions

  • Bill Pearson

    Ten Centuries

    Points: 1359

    Comments posted to this topic are about the item Stairway to DAX and Power BI - Level 9: Function / Iterator Function Pairs: The DAX MAX() and MAXX() Functions

  • DanOrc

    SSC Enthusiast

    Points: 165

    Hello Bill,

    please could you help me with this one:

    I want to find the NEW customers LAST YEAR, I mean I want to find the

    number of NEW customers that bought in the same period last year.

    I know how to calculate the number of total customers last year:

    CALCULATE(DISTINCTCOUNT([ClientID]),SAMEPERIODLASTYEAR(DimCalendar[PK_Date]),all(DimCalendar))

    also the number of new customers ever:

    CALCULATE(

    COUNTROWS(DimCustormer),

    FILTER(

    DimCustormer,

    CALCULATE(COUNTROWS(SalesFact ))>0 &&

    CALCULATE(

    COUNTROWS(SalesFact ),

    FILTER(

    ALL(DimCalendar),

    DimCalendar[PK_Date]<MIN(DimCalendar[PK_Date])

    )

    )=0

    )

    )

    but I really want to also have the NEW Custormers LAST YEAR

    Thank you so much!

    I'm struggling for a week already 🙁

  • denisribeiro

    SSC Enthusiast

    Points: 184

    The article´s title is wrong.
    Its not Power BI but PowerPivot you are using.

  • pietlinden

    SSC Guru

    Points: 62607

    DanOrc - Saturday, February 7, 2015 11:02 AM

    Hello Bill, please could you help me with this one: I want to find the NEW customers LAST YEAR, I mean I want to find thenumber of NEW customers that bought in the same period last year. I know how to calculate the number of total customers last year: CALCULATE(DISTINCTCOUNT([ClientID]),SAMEPERIODLASTYEAR(DimCalendar[PK_Date]),all(DimCalendar))also the number of new customers ever: CALCULATE( COUNTROWS(DimCustormer), FILTER( DimCustormer, CALCULATE(COUNTROWS(SalesFact ))>0 && CALCULATE( COUNTROWS(SalesFact ), FILTER( ALL(DimCalendar), DimCalendar[PK_Date]<MIN(DimCalendar[PK_Date]) ) )=0 ))but I really want to also have the NEW Custormers LAST YEARThank you so much!I'm struggling for a week already 🙁

    Don't you want to get the FIRSTDATE for each customer's sale (across all years). Then you know when they were new.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply