cancel
Showing results 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

Resolver II

Hello,

I need to add a year to the below formula. I tried to add; DATESBETWEEN('Calendar'[Date], DATE(2017,01,01), DATE(2017,31,12) to the formula however when I add as a value it can't display the visual. Is there something that I'm missing or is there another option to add dates?

Count of Active Policy  =

DIVIDE(
DISTINCTCOUNT('Policy'[Active Policy - Accnt ID]),
DISTINCTCOUNT('Account'[Full_Account_ID__c])
)
2 ACCEPTED SOLUTIONS
Super User

Hi @wnicholl ,

``````Your tables will need to be linked in the data model usually Calendar to Policy and Calendar to Account.

Count of Active Policy  =

CALCULATE(
DIVIDE(
DISTINCTCOUNT('Policy'[Active Policy - Accnt ID]),
DISTINCTCOUNT('Account'[Full_Account_ID__c])
)
FILTER(CALENDAR, CALENDAR[DATE]>= DATE(2017,01,01) && CALENDAR[DATE]<= DATE(2017,31,12)))
``````

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Proud to be a Super User!

Super User

Hi,

Does this measure work?

Count of Active Policy  = calculate(DIVIDE(DISTINCTCOUNT('Policy'[Active Policy - Accnt ID]),DISTINCTCOUNT('Account'[Full_Account_ID__c])),DATESBETWEEN('Calendar'[Date]DATE(2017,01,01), DATE(2017,31,12))

Regards,
Ashish Mathur
http://www.ashishmathur.com
5 REPLIES 5
Super User

Hi,

Does this measure work?

Count of Active Policy  = calculate(DIVIDE(DISTINCTCOUNT('Policy'[Active Policy - Accnt ID]),DISTINCTCOUNT('Account'[Full_Account_ID__c])),DATESBETWEEN('Calendar'[Date]DATE(2017,01,01), DATE(2017,31,12))

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resolver II

Super User

Hi @wnicholl ,

``````Your tables will need to be linked in the data model usually Calendar to Policy and Calendar to Account.

Count of Active Policy  =

CALCULATE(
DIVIDE(
DISTINCTCOUNT('Policy'[Active Policy - Accnt ID]),
DISTINCTCOUNT('Account'[Full_Account_ID__c])
)
FILTER(CALENDAR, CALENDAR[DATE]>= DATE(2017,01,01) && CALENDAR[DATE]<= DATE(2017,31,12)))
``````

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Proud to be a Super User!

Resolver II

Super User

Hi @wnicholl ,
You are welcome!

Nathaniel

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors