Add filter for ranges

[oren]

Hi

I have a member database with three columns: one for account ID, one for 'from date' (the start date of the membership), and one for 'end date' (the end date of the membership).

Is there an option to apply a filter based on months, or any other date range, that will display all members who were active within that specified range? For instance, if a member's start date is 1/1/2023, and their end date is 1/1/2024, and I inquire about all active members in March 2023, it would include this specific member in the results.

thank you! 

[v-rzhou-msft]

Hi @oren ,

Here I create a sample to have a test.

I suggest you to create a Calendar table to help calculation.

Calendar = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",FORMAT([Date],"MMMM"),"MonthSort",MONTH([Date]))

Measure:

Filter to show all active members = 
VAR _RANGESTART = MIN('Calendar'[Date])
VAR _RANGEEND = MAX('Calendar'[Date])
RETURN
IF(MAX('Table'[Start Date])<=_RANGEEND && MAX('Table'[End Date])>=_RANGESTART,1,0)

Add this measure into your visual and set it to show items when value = 1. 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.

[JoeBarry]

Hi @oren 

you can try Greg_Deckler 's solution to get a running total https://www.youtube.com/watch?v=meh3OkgFYfc&list=PLoibEIc7heYNZbnY4CTCtg2tjEcMh8WbZ&index=3 

You can then add the date column to a slicer to filter on the Year and Months.

[Ritaf1983]

Hi @oren 
If i understood you correctly please refer to linked tutorial :

https://radacad.com/subscription-pattern-in-power-bi-dax-measure-for-active-subscribers-or-open-tick...

if not 

Please provide sample data that covers your issue or question completely, 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-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

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

[oren]

hi again,

i used this to count subs- and it worked good- thank you!!

but now i ahve anothre step whenm i need to add another measure for the average income for each day ( named as "[LowestPricePerCarat_Calc]" so i semple changed one row to :

var _countRows=AVERAGE('Query1'[LowestPricePerCarat_Calc])

insted of :

var _countRows=countrows(_subscribers)

but i am getting a wrong answar.. 

what did i do wrong ?

thank you!