How to calculate the audit date and assign it to the auditor's

[Jurga]

Hi everyone,

I need to visualization in power BI how many times auditors have audit per quarter. In table have column audits date and auditor. Can you help me with this problem ?

thank you

SAMPLE

 Audit date    Auditor

18.10.2023	John
18.10.2023	John
18.10.2023	John
18.10.2023	John
18.10.2023	John
18.10.2023	George
20.10.2023	George
20.10.2023	George
20.10.2023	George
20.10.2023	George
20.10.2023	George
1.12.2023	George
20.10.2023	George
20.10.2023	Ivy
20.10.2023	Ivy
20.10.2023	Ivy
20.10.2023	Ivy
1.12.2023	Ivy
1.12.2023	Ivy
20.10.2023	Ivy
20.10.2023	Ivy
20.10.2023	Ivy
20.10.2023	Ivy

[Ritaf1983]

Hi @Jurga 
If I understood you correctly the wanted result is something like this :

Then the short solution is to add a column for the quarter :

and basic measure like :

Count audits = COUNTROWS()

pbix is attached 

For a more efficient way and other measures the best practice is to create a dim table with needed columns of periods and work with it.

For more information please refer :

https://www.youtube.com/watch?v=s2FeGeJtzSE

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

[Jurga]

Hi @Ritaf1983 ,

thank you it is helpful but I have one more request regarding calculate date. If I have same date and name must be just one audit.

example:

[Ritaf1983 · Accepted solution]

Hi @Jurga 
modify the measure for :

Count audits distinct = DISTINCTCOUNT('Table'[Date])

The updated Pbix is attached

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

[Arul]

@Jurga ,

Are you expecting this?