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...
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.

     

Ahmedx's avatar
Ahmedx
Icon for Super User rankSuper 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)