Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
54 | |
25 | |
23 | |
15 | |
11 |
User | Count |
---|---|
76 | |
60 | |
46 | |
17 | |
12 |