Re-calculate measure for identified dates

[Anonymous]

Hi All,

I have a schema in which multiple tables are connected and I have a table of measures on these tables. The key table in this schema is a table of Work Orders (WOs) which has a list of dates against which work orders are listed. 

I have now generated a table of start and end dates. I want to re-calculate a measure for these dates as max and min by filtering the WOs table.

I tried this using the below formula but I did not get any result (Just blank):

calculate(

            calculate([Overall System Availability_product]),

            FILTER(WOs,(SELECTEDVALUE(WOs[REPORTDATE+Time])>=SELECTEDVALUE('Table'[Start])) && 

           (SELECTEDVALUE(WOs[REPORTDATE+Time])<=SELECTEDVALUE('Table'[End])))), 

          ALLSELECTED('Test Freq + Config'[SECE Group]), ALLSELECTED(Measures_Table))

I tried this with the filter removed and I got the overall result without taking the start and end dates into consideration. However, I want to filter the WOs data for the start and end dates and ignore the slicer on the page. Any help would be deeply appreciated.

Thanks,

Vijay

[amitjzaveri]

Are you looking for a query something like this?

CALCULATE(
CALCULATE([Overall System Availability_product],DATESBETWEEN(WOs[REPORTDATE+Time], MAX('Table'[Start]),MAX('Table'[End])),ALLSELECTED('Table'))
,ALL('Test Freq + Config'[SECE Group]), ALL(Measures_Table))

[Anonymous]

I think this will give a re-calculation for min and max for the 2 columns. I was hoping to get a value for each row. I hope this makes sense. Thanks. Apologies

[amitchandak]

@Anonymous , Not very clear .

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

@Amit, can you please send me a contact method? I am unable to upload the pbix file here. What I want to do is to generate a trend with the value re-calculated for each date range in the rows of this table. At the moment, it is not taking into consideration the start and end dates in the table which is why the value remains unchanged in the rows

[Anonymous]

Hi @amitchandak ,

Please find the file at the below link:

https://drive.google.com/file/d/17ma0SUX4Kx9X9AGwSyXq2VLX39C_H7Ev/view?usp=sharing

Any help would be deeply appreciated.

Thanks,

Vijay

[Ashish_Mathur]

Hi,

Please share the exact result that you are expecting.

[amitjzaveri]

@Anonymous  Use this formula and I guess it works as per your requirement

Calculated = calculate([Overall System Availability_product],DATESBETWEEN(Measures_Table[Date], MAX('Table'[Start]),MAX('Table'[End])),ALLSELECTED(WOs),ALL('Test Freq + Config'[SECE Group]))

Not sure why you have used "ALLSELECTED(WOs)" but I kept it as it is. And as mentioned earlier DATESBETWEEN will help you to filter rows by given dates

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

Cheers!

---

@Anonymous wrote:

Hi @amitchandak ,

Please find the file at the below link:

https://drive.google.com/file/d/17ma0SUX4Kx9X9AGwSyXq2VLX39C_H7Ev/view?usp=sharing

Any help would be deeply appreciated.

Thanks,

Vijay

---

[Anonymous]

@amitjzaveri , the solution you provided is only working for 1 of the SECEs and not for others and the values dont seem to be for all the ones in table columns 😞

[Anonymous]

Hi all,

I used the below formula and am getting a value for each of the rows in the 'Table' table. These values however are still wrong.

calculate( [Overall System Availability_product],

FILTER(ALLSELECTED(WOs),

(

((WOs[REPORTDATE+Time]>=SELECTEDVALUE('Table'[Start]))) &&

(WOs[REPORTDATE+Time]<=SELECTEDVALUE('Table'[End]))

)),

ALLSELECTED('Test Freq + Config'[SECE Group])

)

Regards,

Vijay

[Anonymous · Accepted solution]

I found the perfect solution for this issue. Here are 2 measures I wrote to arrive at the answer:

measure 1:

Var MinDate = values('Table'[Start])

Var MaxDate = values('Table'[End])

Var No_apm_failfix = CALCULATE(calculate(COUNT(WOs[APM_FAILFIX]),(WOs[APM_FAILFIX]= "APMFAIL" || WOs[APM_FAILFIX]= "APMFAILFIX" )),ALLSELECTED('Test Freq + Config'[SECE Group]),WOs[REPORTDATE+Time]<=MaxDate, WOs[REPORTDATE+Time]>=MinDate)

Var No_CM = CALCULATE(CALCULATE(COUNT(WOs[WORKTYPE]), (WOs[CM_HASRAF] == "CMY")), WOs[REPORTDATE+Time]<=MaxDate, WOs[REPORTDATE+Time]>=MinDate)

Var calc_dt = CALCULATE(sum(WOs[DOWNTIME]), (WOs[CM_HASRAF] == "CMY"), WOs[REPORTDATE+Time]<=MaxDate, WOs[REPORTDATE+Time]>=MinDate)

Var date_diff = DATEDIFF(MinDate, MaxDate, DAY)

Var pop = calculate(SUM('Test Freq + Config'[Sum of Pop Counts]), REMOVEFILTERS(WOs[REPORTDATE+Time]))

Var hidden_fail = IFERROR(CALCULATE(No_apm_failfix/(date_diff*pop)),1)

Var hidden_av = iferror(if(hidden_fail>0, (1-(exp(-(hidden_fail*sum('Test Freq + Config'[Test Freq])))))/(hidden_fail*sum('Test Freq + Config'[Test Freq])),1),1)

Var CM_unav = if(calc_dt == 0,0,if(pop == 0, 0,calculate(calc_dt/(date_diff*pop),WOs[REPORTDATE+Time]>=MinDate, WOs[REPORTDATE+Time]<=MaxDate)))

Var element_av = IFERROR(calculate(hidden_av - CM_unav),1)

// Var comb_av = calculate(

// if((VALUES('Test Freq + Config'[Configuration]) == "1oo1"), element_av,if((values('Test Freq + Config'[Configuration]) == "2oo2"), power(element_av,2), if((values('Test Freq + Config'[Configuration]) == "N/A"), 1,if((values('Test Freq + Config'[Configuration]) == "1oo2"),(1 - power(1-element_av,2)),if((values('Test Freq + Config'[Configuration]) == "3oo4"), (4*power(element_av,3) - 3*power(element_av,4)),0))))), ALLSELECTED('Test Freq + Config'[SECE Group]),WOs[REPORTDATE+Time]>=MinDate, WOs[REPORTDATE+Time]<=MaxDate)

Var overall =

CALCULATE(PRODUCTX(

KEEPFILTERS(values('Test Freq + Config'[RBD Group])),

CALCULATE(calculate(

if((VALUES('Test Freq + Config'[Configuration]) == "1oo1"), element_av,

if((values('Test Freq + Config'[Configuration]) == "2oo2"), power(element_av,2),

if((values('Test Freq + Config'[Configuration]) == "N/A"), 1,

if((values('Test Freq + Config'[Configuration]) == "1oo2"),(1 - power((1-element_av),2)),

if((values('Test Freq + Config'[Configuration]) == "3oo4"), (4*power(element_av,3) - 3*power(element_av,4)),1))))),

WOs[REPORTDATE+Time]>=MinDate, WOs[REPORTDATE+Time]<=MaxDate))

))

return

element_av

final measure:

CALCULATE(PRODUCTX(

KEEPFILTERS(values('Test Freq + Config'[RBD Group])),

CALCULATE(calculate(

if((VALUES('Test Freq + Config'[Configuration]) == "1oo1"), [Calculated],

if((values('Test Freq + Config'[Configuration]) == "2oo2"), power([Calculated],2),

if((values('Test Freq + Config'[Configuration]) == "N/A"), 1,

if((values('Test Freq + Config'[Configuration]) == "1oo2"),(1 - power((1-[Calculated]),2)),

if((values('Test Freq + Config'[Configuration]) == "3oo4"), (4*power([Calculated],3) - 3*power([Calculated],4)),1))))))

)))

[Anonymous]

Thanks @amitchandak ,

I tried the solution you gave me but it looks like it now does not change with the selection of SECE group in the page. The same result is displayed for all SECE groups 😞

@Ashish_Mathur : The result I expected was:

recalculated values of the verall availability for the given dates for the selected SECE group. Unfortunately, I am unable to calculate them here because even with the help @amitchandak provided, the result is not specific to the SECE group. 😞

Regards,

Vijay

[Anonymous]

Thanks Amit. I will share a sample