Improve mesaure resources exceed in Power BI Service

[Syndicate_Admin]

Hello

I will appreciate if somebody can help me to improve the performance of this measure. In desktop I can see in the object perfecty, but in -Service it excess resources even if I filtrer only one reference.

I am trying to obtain the measure of the next 22 weeks and select the minimum value.

If any advise about how to calculate more efficiently, I would also appreciate.

this is the measure:

Traffic light 22SEM = where SEM1 = calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+7,day))

where SEM2 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+14,DAY))) where SEM3 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+21,DAY))) where SEM4 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+28,DAY))) where SEM5 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+35,DAY))) where SEM6 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+42,DAY)))where SEM7 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+49,DAY))) where SEM8 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+56,DAY))) where SEM9 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+63,DAY)))where SEM10 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+70,DAY))) where SEM11 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+77,DAY))) where SEM12 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+84,DAY))) where SEM13 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+91,DAY))) where SEM14 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+98,DAY))) where SEM15 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+105,DAY))) where SEM16 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+112,DAY))) where SEM17 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+119,DAY))) where SEM18 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+126,DAY)))where SEM19 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+133,DAY))) where SEM20 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+140,DAY))) where SEM21 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+147,DAY)))where SEM22 = (calculate('Measures'[AccDisponible real Rolling],DATEADD('Calendar'[Date],+154,DAY))) WHERE SEMAF2=CALCULATE(Measurement[AccDisponible real Rolling])

return

if(values(Mastermaterialsap[DChain-spec.])="51",Measurement[Total months available],minx({ SEM1,SEM2,SEM3,SEM4,SEM5,SEM6,SEM7,SEM8,SEM9,SEM10,SEM11,SEM12,SEM13,SEM14,SEM15,SEM16,SEM17,SEM18,SEM19,SEM20,SEM21,SEM22,SEMAF2},[Value]))

many thanks

Sonia

[amitchandak]

@Syndicate_Admin , using a week rank column in date table joined with date of you table you can have measure like

22 week MAxx = CALCULATE(maxx(Values('Date'[Week Year]),[AccDisponible real Rolling])
,filter(all('Date'), 'Date'[Week Rank] >= Max('Date'[Week Rank]) && 'Date'[Week Rank] <= Max('Date'[Week Rank])+22))

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s