Count active ids by day, month quarter Year

[Anonymous]

Hi I am attaching my sample file in this file Please see Page 1, i have calculated column named "Count of Reservation" and one measure named "Count". THe bar chart on page 1 is giving me correct result on all time periods but no slicers are working with this. Please help me resolve this issue. 

Link of pbix

Calculated Column: Count of reservations = CALCULATE(COUNT(Reservation[ID]),FILTER(Reservation,Reservation[Fromdate]<=Dates[Date]),FILTER(Reservation,Reservation[Todate]>=Dates[Date]))

Measure Count = Calculate(Sum(Dates[Count of reservations] )/365)

Date table = 

Dates = CALENDAR(MIN(Reservation[Fromdate]),MAX(Reservation[Todate]))

Reservation Table 

ID                Fromdate            Todate              Type

1	7/1/2017	12/31/2017	1
2	7/1/2018	6/30/2019	3
3	7/1/2017	9/30/2017	1
4	10/1/2017	12/31/2017	1
5	1/1/2018	3/31/2018	2
6	4/1/2018	6/30/2018	2
7	7/1/2018	9/30/2018	2
8	10/1/2018	12/31/2018	2
9	1/1/2019	3/31/2019	3
10	8/1/2017	9/30/2017	1

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may create two measures as below:

Measure =
COUNTX (
    FILTER (
        GENERATE ( Dates, Reservation ),
        Dates[Date] >= Reservation[Fromdate]
            && Dates[Date] <= Reservation[Todate]
    ),
    Reservation[ID]
)

Measure 2 = [Measure]/365

Regards,

Cherie

[Anonymous]

Thanks alot for your help

[Greg_Deckler]

Not seeing the PBIX. Perhaps has something to do with your relationships between tables but tough to say.

[Anonymous]

https://drive.google.com/open?id=1X80W_MPIcVFbuyu8NjloMGumfMRjEeXl

Here is the file

[Anonymous]

Please see Page 1 in the file and Count of Reservation calculated column and Count Measure.

[Anonymous]

Thanks for your reply. Sorry for the file I will upload and give link

[Anonymous]

There is no relationship between columns because I have 2 date columns in reservation table