cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrmiyagi
Helper III
Helper III

Calculate frequency in year period and check if equals another value

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
1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

Hi, @mrmiyagi 

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

3 REPLIES 3
v-robertq-msft
Community Support
Community Support

Hi, @mrmiyagi 

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

Great thank you this will work!

Ashish_Mathur
Super User III
Super User III

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/

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors