Create measure to show the Audit count for previous months with

[Anonymous]

I have 2 tables 
Table Employee_CPM (Employee ID , CPMID , Date, RoleID, ProjectID , SiteID, ClientID) 
Table CPM_Audit ( CPMID , AuditorID , AuditDate)
the relation is Many To Many from Employee_CPM to CPM_Audit based on CPMID
- CPMID is created for each month and auditor can audit for upto last 4 months
- now i want to write a measure to find the no of audit done with in the selected year and month with option to filter it by ProjectID, SiteID and ClientID

[Anonymous · Accepted solution]

@lbendlin  Thanks for your contribution on this thread.

Hi @Anonymous ,

You can create a measure as below to get the number of auditor:

Measure =
VAR SelectedYear =
    YEAR ( MAX ( 'Employee_CPM'[Date] ) )
VAR SelectedMonth =
    MONTH ( MAX ( 'Employee_CPM'[Date] ) )
VAR MinDate =
    EOMONTH ( DATE ( SelectedYear, SelectedMonth, 1 ), -4 ) + 1
VAR MaxDate =
    EOMONTH ( DATE ( SelectedYear, SelectedMonth, 1 ), 0 )
VAR Pid =
    SELECTEDVALUE ( 'Employee_CPM'[ProjectID] )
VAR Sid =
    SELECTEDVALUE ( 'Employee_CPM'[SiteID] )
VAR cid =
    SELECTEDVALUE ( 'Employee_CPM'[ClientID] )
RETURN
    CALCULATE (
        COUNT ( 'CPM_Audit'[CPMID] ),
        FILTER (
            'CPM_Audit',
            'CPM_Audit'[AuditDate] >= MinDate
                && 'CPM_Audit'[AuditDate] <= MaxDate
        ),
        FILTER (
            'Employee_CPM',
            'Employee_CPM'[ProjectID] = Pid
                && 'Employee_CPM'[SiteID] = Sid
                && 'Employee_CPM'[ClientID] = cid
        )
    )

If the above one can't help you get the expected result, please provided the necessary info just as suggested by @lbendlin . It would be very helpful to find the solution. Thank you.

Best Regards

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...