Distinct count Active User for 3 last month

[yanivsab]

Hi 

I Need to create a measure that show the distinct users that didn't made an activity for the last 3 months.

Table columns  :

- UserId ( Number )

-TimeFrame ( Date format ) 

- Has Activity (TRUE/FALSE Boolean Column)

Below is the measure i build which is not given me the correct picture ( i didn't success to filter the boolean column) 

The measure actually give me the distinct users that didn't made an activity  for each month while i would like to have  the last 3 months without activity.

InactiveUser_SPO = CALCULATE(DISTINCTCOUNT(_UserActivity_Export[UserId]),filter(_UserActivity_Export,_UserActivity_Export[TimeFrame].[Month] in {"August","september","october"} ))

i will appriciate your help

Thanks in advanced 

Yaniv

[Ashish_Mathur]

Hi,

Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number.  Create a relationship (Many to One) and Single from the Timeframe column of the User Activity Export table to the Date column of the Calendar Table.  To your Table visual, drag User ID.  This DAX measure pattern should work

Measure = countrows(filter(summarize(calculatetable(calendar,datesbetween(calendar[date],edate(eomonth(today()),-4)+1,eomonth(today(),-1))),values(_UserActivity_Export[UserId]),"A",calculate(countrows(_UserActivity_Export),_UserActivity_Export[Has Activity]=true),[A]=0))

Hope this helps.  If it does not, then share the download link of the PBI file and show the expected result.

[Ritaf1983 · Accepted solution]

Hi @yanivsab 
Generally, you can try to check the linked tutorials:

https://www.youtube.com/watch?v=m1uupRDWzQw

https://www.youtube.com/watch?v=cfDyNPQIJtA

And the linked discussion:

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Customers-without-purchases-in-gt-6-...

For a more specific solution, please attach the data to work with in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ahmedx]

Share some data to work with (in a format the can be pasted in an MS Excel file) and show the expected result.