find cumulative count following monthly criteria

[lawada]

i want to calculate distinct cummulative count of users by month in the follwing criteria
assume we have:

total users	month
30	may2022
20	april2022
21	march2022
19	february2022

i want to find distinct cumulatice count for each month plus the previous month for example, for the month of may i want to see distinct cumulative active users from may and april and for april i want to see active users from april and march and same thing for march and feb assume the result will be now:

total users	month
38	may2022
35	april2022
25	march2022
23	february2022

and following the same criteria to find cumulative count for the each month with 2 previous month
for example,for the month of may i want to see active users from may and april and march and for april i want to see active users from april and march and february and same thing for january
assume the result will be

total users	month
53	may2022
50	april2022
40	march2022
33	february2022

after that i want to apply same calculation for 3,4,5 previous months

what kind of dax and functions can help me achieve the calculation?

[v-yiruan-msft · Accepted solution]

Hi  @lawada ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. 

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Whitewater100]

Hi:

This one definitely calls for a datetable, continuous and makerd as date table with relationship to your fact table with the user info.

You can start off with a measure fr distinctcount of the users DISTINCT # Users = DISTINCTCOUNT(TableName[UserID])

DistinctCount Last 3 months = CALCULATE([DISTINCT # Users], 

DATEADD(LASTDATE(Dates[Month]), -3,MONTH)

There other functions like DATESBETWEEN & DATESINPERIOD and PARALELPERIOD that accomplish similiar results.

Here is ParallelPeriod = 

CALCULATE([DISTINCT # Users],PARALLELPERIOD('Dates'[Date],-2,MONTH))  This will bring overallcount for two months ago.

There's even a PREVIOUSMONTH function. 

PM = CALCULATE([[DISTINCT # Users]], PREVIOUSMONTH(Dates[Date]))

I will paste datetable code if you need it(table Nane = "Dates"). Use the fields from Date table in visuals. MODELING>NEWTABLE>

Dates =

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today() + 243

VAR FiscalMonthEnd = 12

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

-- Add the Year for each individual date

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

-- Add the calendar month and other month related data for each date

VAR Months = ADDCOLUMNS(

    Years,

    "Month",MONTH([Date]),

    "Year and Month Number",FORMAT([Date],"YYYY-MM"),

    "Year and Month Name",FORMAT([Date],"YYYY-MMM"),

    "Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

    "Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

        IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

-- Add the Quarter and other quarter related data for each date   

VAR Quarters = ADDCOLUMNS(

    Months,

    "Quarter",ROUNDUP(MONTH([Date])/3,0),

    "Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

-- Add the Day and other day related data for each date   

VAR Days = ADDCOLUMNS(

    Quarters,

    "Day",DAY([Date]),

    "Day Name",FORMAT([Date],"DDDD"),

    "Day Of Week",WEEKDAY([Date]),

    "Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date   

VAR Weeks = ADDCOLUMNS(

    Days,

    "Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

    "Week of Year (Sunday)",WEEKNUM([Date],1),

    "Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

var WorkingDays = ADDCOLUMNS(

    Weeks,

    "Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

RETURN WorkingDays

I hope this helps