Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Calculate frequency in year period and check if equals another value

‎04-29-2021 07:48 AM

Hello, 

 

I could use some help with building a DAX measure that can determine the amount of times in a year period the count of booked dates for each WO assetID and then see if it is equal or greater than a value(Standard), the end output should be a "Pass" or "Fail". I need some help with including time intelligence into the PassFail measure. 

 

WorkOrder_Table 

WO assetIDWO PM CodeYearMonthDistinct count Booked Date
C15-DRYPOND-01PM-WQPF-012016April1
C15-DRYPOND-01PM-WQPF-012016May1
C15-DRYPOND-01PM-WQPF-012016June1
C15-DRYPOND-01PM-WQPF-012016July1
C15-DRYPOND-01PM-WQPF-012016August2
C15-DRYPOND-01PM-WQPF-012016September1
C15-DRYPOND-01PM-WQPF-012016October1
C15-DRYPOND-01PM-WQPF-012016November2
C15-DRYPOND-01PM-WQPF-012016December1
C15-DRYPOND-01PM-WQPF-012017January1
C15-DRYPOND-01PM-WQPF-012017February1
C15-DRYPOND-01PM-WQPF-012017March1
C15-DRYPOND-01PM-WQPF-012017April1
C15-DRYPOND-01PM-WQPF-012017May1
C15-DRYPOND-01PM-WQPF-012017June1
C15-DRYPOND-01PM-WQPF-012017July1
C15-DRYPOND-01PM-WQPF-012017August1
C15-DRYPOND-01PM-WQPF-012017September1
C15-DRYPOND-01PM-WQPF-012017October1
C15-DRYPOND-01PM-WQPF-012017November1
C15-DRYPOND-01PM-WQPF-012017December1
C15-DRYPOND-01PM-WQPF-012018January1
C15-DRYPOND-01PM-WQPF-012018February1
C15-DRYPOND-01PM-WQPF-012018March1
C15-DRYPOND-01PM-WQPF-012018April1
C15-DRYPOND-01PM-WQPF-012018May1
C15-DRYPOND-01PM-WQPF-012018August1
C15-DRYPOND-01PM-WQPF-012018September1
C15-DRYPOND-01PM-WQPF-012018November1
C15-DRYPOND-01PM-WQPF-032016June1
C15-DRYPOND-01PM-WQPF-032016October1
C15-DRYPOND-01PM-WQPF-032017June1
C15-DRYPOND-01PM-WQPF-032017November1
C15-DRYPOND-01PM-WQPF-032018July1
C15-DRYPOND-01PM-WQPF-032018November1
C15-DRYPOND-01PM-WQPF-262019March1
C15-DRYPOND-01PM-WQPF-262019April1
C15-DRYPOND-01PM-WQPF-262019July2
C15-DRYPOND-01PM-WQPF-262019December1
C15-DRYPOND-01PM-WQPF-262020June1
C15-DRYPOND-01PM-WQPF-272019July1
C15-DRYPOND-01PM-WQPF-272020June1

 

Frequency_Check

PM IDFrequencyStandard
 PM-WQPF-047MONTHLY1
 PM-WQPF-26MONTHLY1
 PM-WQPF-27MONTHLY1
 PM-WQPF-29MONTHLY1
 PM-WQPF-30MONTHLY1
 PM-WQPF-32MONTHLY1
 PM-WQPF-33MONTHLY1
 PM-WQPF-35MONTHLY2
 PM-WQPF-36MONTHLY1
 PM-WQPF-37MONTHLY1
 PM-WQPF-38MONTHLY1
 PM-WQPF-39MONTHLY1
 PM-WQPF-40MONTHLY1
 PM-WQPF-40YEAR2
 PM-WQPF-42MONTHLY1
 PM-WQPF-43MONTHLY1
 PM-WQPF-43YEAR2
 PM-WQPF-44MONTHLY1
 PM-WQPF-44YEAR2
PM-WQPF-10MONTHLY1
PM-WQPF-25MONTHLY1
PM-WQPF-26YEAR2
PM-WQPF-36MONTHLY1
PM-WQPF-38QUARTERLY1
PM-WQPF-39YEAR1

 

Current Measures: 

 

 

 

TotalBookedDates = SUM(WorkOrder_Table[Distinct count Booked Date])
TotalStandard = SUM(Frequency_Check[Standard])
PassFail = 
VAR m_check = [TotalBookedDates]
RETURN
IF(m_check >= [TotalStandard],"Pass","Fail")

 

 

 

 

Desired Output:

YearMonthWO assetIDWO PM CodeDistinct count Booked DateFrequencyStandardMeasure
2019MarchC15-DRYPOND-01PM-WQPF-261YEAR2Fail
2019MarchP26-DRYPOND-04PM-WQPF-261YEAR2Fail
Labels:
Message 1 of 4
563 Views
1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

‎05-03-2021 08:22 PM

Hi, @Anonymous 

According to your description, I can roughly understand your requirement, I think you can achieve this using “Merge query” in the power query and a calculated column, you can try my steps:

  1. Go to the power query editor, merge tables like this:

v-robertq-msft_0-1620098437979.png

v-robertq-msft_1-1620098437984.png

 

  1. Then expand the table, apply and close, create a calculated column in the new table:
Result =

IF(

[Distinct count Booked Date]>=[Frequency_Check.Standard],"Pass","Fail")
  1. Then create a slicer and a table chart like this:

v-robertq-msft_2-1620098437998.png

 

And you can get what you want.

 

You can download my test pbix file below

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

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

Calculate frequency in year period and check if.pbix
Message 3 of 4
458 Views
3 REPLIES 3
v-robertq-msft
Community Support
Community Support

‎05-03-2021 08:22 PM

Hi, @Anonymous 

According to your description, I can roughly understand your requirement, I think you can achieve this using “Merge query” in the power query and a calculated column, you can try my steps:

  1. Go to the power query editor, merge tables like this:

v-robertq-msft_0-1620098437979.png

v-robertq-msft_1-1620098437984.png

 

  1. Then expand the table, apply and close, create a calculated column in the new table:
Result =

IF(

[Distinct count Booked Date]>=[Frequency_Check.Standard],"Pass","Fail")
  1. Then create a slicer and a table chart like this:

v-robertq-msft_2-1620098437998.png

 

And you can get what you want.

 

You can download my test pbix file below

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

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

Calculate frequency in year period and check if.pbix
Message 3 of 4
459 Views
Anonymous
Not applicable
In response to v-robertq-msft

‎05-04-2021 09:54 AM

Great thank you this will work!

Message 4 of 4
448 Views
0
Ashish_Mathur
Super User
Super User

‎04-30-2021 04:53 PM

Hi,

What problem do you face with your existing measures?  Do you not get the desired result?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 2 of 4
480 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

View All