Measure is giving me blank

[PowerBI88]

Hi,

I am trying to calculate weekly forecasting, but the following measure returns blank:

Forecasting Pw =

Var CurrentWeek = SELECTEDVALUE(AzureUsageRecords[WeekNumber])

var CurrentYear = SELECTEDVALUE(AzureUsageRecords[DateCost].[Year])

var MaxWeekNumber = CAlculate(max(AzureUsageRecords[WeekNumber]), ALL(AzureUsageRecords))

RETURN

SUMX(

FILTER(ALL(AzureUsageRecords),

if(CurrentWeek = 1,

AzureUsageRecords[WeekNumber] = MaxWeekNumber && AzureUsageRecords[DateCost].[Year] = CurrentYear -1,

AzureUsageRecords[WeekNumber] = CurrentWeek - 1 && AzureUsageRecords[DateCost].[Year] = CurrentYear)), 'RegressionelineareApplicazoni(Fine Mese)'[ForecastingEUR])

Any hint on why it is behaving this way?

Thank you

Kind regards

Marta

[PowerBI88 · Accepted solution]

I managed to solve my problem, by creating the measure on the correct date table.

[vojtechsima]

@PowerBI88 
That's amazing news, sorry for not answering lately.
If you could please share your solution and accept it as Solution, so you can close this thread and other people can find it useful.

[vojtechsima]

Hi, @PowerBI88 
From quick observation, it looks okey, could you share a sample dataset (copyable) so I could try it on my own, please?

[PowerBI88]

My dataset is full of sensitive data, I don't know how to mask it.

I checked the output with a table diagram and it is actually not blank, but the value for Forecasting PW is wrong:

I think also it is important to say that the value Forescasting Eur comes from a different table with respect to AzureUsage records (RegressionelineareApplicazoni(Fine Mese)'[ForecastingEUR])) and that the two tables are not directly related.

Kind regards

[vojtechsima]

HI, @PowerBI88 
Hmm, maybe you could create a Data Calendar and make a relationship to both tables and then use it in the calculation. IT could help.

Also,
I understand you can'T show me the exact data. However, if you could create a "fake" table with random values which would have the same structure, that would help me a lot.
Just make something in excel and Copy it here, so I can paste it directly to Power BI.

[PowerBI88]

This is AzureUsage records Table:

Costo	DateCost	_MonthNumber	_DayNoOfYear	WeekNumber	Year	Year&Week	Forecasting Pw
0,00 €	28/02/2022 00:00	2	59	9	2022	2022-9	33.000
0,00 €	28/02/2022 00:00	2	59	9	2022	2022-9	44.00
0,00 €	28/02/2022 00:00	2	59	9	2022	2022-9	44.01
0,00 €	28/02/2022 00:00	2	59	9	2022	2022-9	44.02
0,00 €	28/02/2022 00:00	2	59	9	2022	2022-9	44.03
0,00 €	28/02/2022 00:00	2	59	9	2022	2022-9	44.04

This is RegressionelineareApplicazoni(Fine Mese)

Applicazione,ForecastingEUR

app01,75111 €

app02,450272 €

app03,10838 €

this is the chain of relationships:

Kind regards

Marta