Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Surya9
Resolver III
Resolver III

Create measure to show the Audit count for previous months with

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

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

@lbendlin  Thanks for your contribution on this thread.

Hi @Surya9 ,

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

@lbendlin  Thanks for your contribution on this thread.

Hi @Surya9 ,

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lbendlin
Super User
Super User

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...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.