DAX Measure

[Anonymous]

I have Project Table, some necessary columns are given below:

Project Case	Organization	StartPeriod	EndPeriod
42251	XYZ	8/27/2017	10/16/2017

I have another table Tickets, which looks like below:

Case	Organization	StartPeriod	EndPeriod
52454	XYZ	8/29/2017	9/10/2017
56464	XYZ	11/16/2017	12/12/2017
54859	XYZ	11/19/2017	12/12/2017
52421	XYZ	1/9/2018	2/2/2018

I want to create separate measures for the below:
1) Case in Tickets table created between the StartPeriod and EndPeriod of Project Case.
2) Case in Tickets table create during the first 30 days after the EndPeriod of Project Case.

Any help 

[smpa01 · Accepted solution]

@Anonymous  so as I understand, Measure1 is working as intended but Measure2 is not with the revised dataset.

Try this out

Measure2 =
VAR _0 =
    CALCULATE (
        MAX ( Project[EndPeriod] ),
        FILTER (
            VALUES ( Project[Organization] ),
            Project[Organization] = CALCULATE ( MAX ( Tickets[Organization] ) )
        )
    )
VAR _1 =
    CALCULATE (
        COUNT ( Tickets[Case] ),
        FILTER ( Tickets, Tickets[StartPeriod] > _0 && Tickets[StartPeriod] <= _0 + 30 )
    )
RETURN
    _1

[Anonymous]

@smpa01 This worked well! Thanks
Instead of Count I just used Distinctcount, as there were duplicates in my data. Just wanted to update if anyone refers to this solution.

[smpa01]

@Anonymous 

Measure1 = 
CALCULATE (
    COUNT ( Tickets[Case] ),
    FILTER (
        Tickets,
        Tickets[StartPeriod] >= MAX ( Project[StartPeriod] )
            && Tickets[StartPeriod] <= MAX ( Project[EndPeriod] )
    )
)

Measure2 = 
CALCULATE (
    COUNT ( Tickets[Case] ),
    FILTER (
        Tickets,
         Tickets[StartPeriod] <= MAX ( Project[EndPeriod] )+30
    )
)

[Anonymous]

@smpa01 For Measure 2, I am not getting correct results.
Every Organization Starts with a Project Case, it may last upto N number of days depending upon how long it took.
But I need to know the Cases submitted in the First 30 days when the Project Case got Closed(EndDate)
For Ex: If Project Case took the Entire August Month to complete and get Closed(8/31/2021). I need to know the Cases submitted in the Month of September (30 days).

[smpa01]

@Anonymous  my solutuion was based on the data provided.

Improve your question with better sample data if you are not getting the result you desire.

[Anonymous]

@smpa01 I just updated the dates for the case
Project Table

Project case	Organization	StartPeriod	EndPeriod
42251	XYZ	8/6/2017	8/31/2017

Tickets Table

Case	Organization	StartPeriod	EndPeriod
52454	XYZ	8/28/2017	9/6/2017
56464	XYZ	9/6/2017	9/18/2017
54859	XYZ	9/7/2017	9/26/2017
52421	XYZ	9/8/2017	9/9/2017

Here, when you look at the dates. The project case for Organization XYZ was started and completed in August on 8/31/2017.

For Measure 2, I want the cases from Tickets Table where the StartPeriod is after 8/31/2017 for first 30 days (i.e from 9/1/2017 to 9/30/2017)
Makes Sense?

Any recommendations @Greg_Deckler @amitchandak