Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

dax

p1m =
CALCULATE(
    DISTINCTCOUNT(PSR[customer_code]),
    FILTER(
        VALUES(PSR[customer_code]),
        [sum of ret] > 0
    )
)

this is a measure that calculates distinct customer count of customers whose sum of retailing is >0.
i want to calculate another measure that shows distinct count of customer of this month+last 2 month in the current month.
eg count of november would be nov+oct+sept. similarly count of dec would be dec+nov+oct.
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    I suggest you to create a "DimDate" table to help calculation.

    DimDate = 
VAR _CALENDAR =
    CALENDARAUTO()
RETURN
    ADDCOLUMNS (
        _CALENDAR,
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] ),
        "MonthName", FORMAT ( [Date], "MMMM" ),
        "RANKX",
            RANKX ( _CALENDAR, YEAR ( [Date] ) * 100 + MONTH ( [Date] ),, ASC, DENSE )
    )

    Measure:

    Sum 3 month count = 
VAR _SUMMARIZE =
    SUMMARIZE (
        ALL ( DimDate ),
        DimDate[Year],
        DimDate[MonthName],
        DimDate[RANKX],
        "P1M", [p1m]
    )
RETURN
    SUMX (
        FILTER (
            _SUMMARIZE,
            [RANKX]
                > MAX ( DimDate[RANKX] ) - 3
                && [RANKX] <= MAX ( DimDate[RANKX] )
        ),
        [P1M]
    )

    Result is as below.

     

    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.

     

5 Replies

  • Kishore_KVN's avatar
    Kishore_KVN
    Solution Sage
    2 years ago

    Hello Anonymous ,

    Firstly create a calendar table and create relationship with your fact table. 

    Then use below calculation by modifying table names as per your data.

    Cal_Measure =
Var CM = CALCULATE(DISTINCTCOUNT(PSR[customer_code]), Filter(Calendar_Table, 'Calendar_Table'[Month] = Month(Today())))
Var PM = CALCULATE(DISTINCTCOUNT(PSR[customer_code]), Filter(Calendar_Table, 'Calendar_Table'[Month] = Month(Today())-1))
Var PPM = CALCULATE(DISTINCTCOUNT(PSR[customer_code]), Filter(Calendar_Table, 'Calendar_Table'[Month] = Month(Today())-2))
Return
CM+PM+PPM

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      not displaying anything

       

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    pls try this

     

    CURRENT MONTH nov = 
VAR _Start = EOMONTH(TODAY(),-1)+1

VAR _End = EOMONTH(TODAY(),0)
RETURN
CALCULATE([p1m], DATESBETWEEN('Calendar'[Date],_Start,_End)
-------------
Last 1 MONTH Oct = 
VAR _Start = EOMONTH(TODAY(),-2)+1

VAR _End = EOMONTH(TODAY(),-1)
RETURN
CALCULATE([p1m], DATESBETWEEN('Calendar'[Date],_Start,_End)
------------
Last 2 MONTH Sept= 
VAR _Start = EOMONTH(TODAY(),-3)+1

VAR _End = EOMONTH(TODAY(),-2)
RETURN
CALCULATE([p1m], DATESBETWEEN('Calendar'[Date],_Start,_End)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    I suggest you to create a "DimDate" table to help calculation.

    DimDate = 
VAR _CALENDAR =
    CALENDARAUTO()
RETURN
    ADDCOLUMNS (
        _CALENDAR,
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] ),
        "MonthName", FORMAT ( [Date], "MMMM" ),
        "RANKX",
            RANKX ( _CALENDAR, YEAR ( [Date] ) * 100 + MONTH ( [Date] ),, ASC, DENSE )
    )

    Measure:

    Sum 3 month count = 
VAR _SUMMARIZE =
    SUMMARIZE (
        ALL ( DimDate ),
        DimDate[Year],
        DimDate[MonthName],
        DimDate[RANKX],
        "P1M", [p1m]
    )
RETURN
    SUMX (
        FILTER (
            _SUMMARIZE,
            [RANKX]
                > MAX ( DimDate[RANKX] ) - 3
                && [RANKX] <= MAX ( DimDate[RANKX] )
        ),
        [P1M]
    )

    Result is as below.

     

    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.