Help!! Store output of a measure

[Anonymous]

Hi,

I have the following table call [Table]:

Where Number and Date are given

IE and FE are calculated columns and are given by the user.

IE = DATE(2019;09;01)

FE = DATE(2020;01;01)

And total is calculated column and is:

Total = IF(AND('Table'[Date]<'Table'[FE];'Table'[Date]>'Table'[IE]);DATEDIFF('Table'[Date];'Table'[FE];HOUR);0)

Then i create the following measure:

Medida = AVERAGE('Table'[Total])

Now, I add to the model the following table call [Dates]:

 What I need is evaluate [Medida] with IE=FI and FE=FF, this means repeat the formula in the measure [Medida] changing the inputs IE and FE depending on which row of [Table] is and store the result in a new table or column.

My expected results if I store the result in a new column in [Table] (for the first rows are):

I already  try a calculated column in table like this:

Columna = CALCULATE(FORMAT([Medida];"General Number");FILTER('Table';AND('Table'[IE]=Dates[FI];'Table'[FE]=Dates[FF])))

But it does not work because IE and FE in [Table] (First image in the post) are the same date in every row.

Do you have any idea?

Thanks.

[Anonymous]

I have the following table call [Table]:

Where Number and Date are given

IE and FE are calculated columns and are given by the user.

IE = DATE(2019;09;01)

FE = DATE(2020;01;01)

And total is calculated column and is:

Total = IF(AND('Table'[Date]<'Table'[FE];'Table'[Date]>'Table'[IE]);DATEDIFF('Table'[Date];'Table'[FE];HOUR);0)

Then i create the following measure:

Medida = AVERAGE('Table'[Total])

Now, I add to the model the following table call [Dates]:

 What I need is evaluate [Medida] with IE=FI and FE=FF, this means repeat the formula in the measure [Medida] changing the inputs IE and FE depending on which row of [Table] is and store the result in a new table or column.

My expected results if I store the result in a new column in [Table] (for the first rows are):

I already  try a calculated column in table like this:

Columna = CALCULATE(FORMAT([Medida];"General Number");FILTER('Table';AND('Table'[IE]=Dates[FI];'Table'[FE]=Dates[FF])))

But it does not work because IE and FE in [Table] (First image in the post) are the same date in every row.

Do you have any idea?

Thanks.

[UKNSI-Powerbi]

Hi,

I have a similar issue: forecast table with a measure which calculate dinamically Net 

Model			1-Arrears before week 2403		Week 2404           21/01/24 27/01/24	Week 2405           28/01/24 03/02/24	Week 2406           04/02/24 10/02/24	Week 2407           11/02/24 17/02/24
Part	Description	Tot Stock	Demand	Supply	Net	Demand	Supply	Net	Demand	Supply	Net	Demand	Supply	Net	Demand	Supply	Net
R0042001599	BANANAS	48207	440	40000	87767	5478		82289	6800	20000	95489	9072		86417	13608		72809
R0042001619	APPLES	13607	58	1600	15149			15149	720		14429	400		14029	312		13717
R0042001649	PEARS	20193	1868		18325	2200	12000	28125	1386	8000	34739	3150		31589	3780		27809

The current Net measure works fine

• Starting point Tot stock+demand-supply=Net
• Following weeks Previous Net+demand-supply=New Net

Current measure

Net measure: = 
VAR _initialstock =
     MAX ( 'Net measure V2'[TOTSTOCK] )
VAR _newtablesupplydemand =
    SUMX (
        FILTER (
            ALL ( 'Net measure V2' ),
            'Net measure V2'[Item] = MAX ( 'Net measure V2'[Item] )
&& 'Net measure V2'[Week] <= MAX ( 'Net measure V2'[Week] )
        ),
        'Net measure V2'[Supply] - 'Net measure V2'[Demand]
    )
RETURN
    _initialstock + _newtablesupplydemand

What I would like to achieve is a next step forecast: is there some way from week 1 to know Net of week 4 (basically next month)

Here an example: for item APPLE At first week I would like to know forecast week 2406 Net is 86417

Model 01			1-Arrears before week 2403		Week 2406           04/02/24 10/02/24
Part	Description	Tot Stock	Demand	Supply	Net	Demand	Supply	Net
R0042001599	BANANAS	48207	440	40000	87767	9072		86417

[Anonymous · Accepted solution]

I solved it with the following function:

Tabla = ADDCOLUMNS(SUMMARIZE(Dates;Dates[FF];Dates[FI]);"A1";AVERAGEX('Table';IF(AND('Table'[Date]>Dates[FI];'Table'[Date]<Dates[FF]);DATEDIFF('Table'[Date];Dates[FF];HOUR);0)))

It´s not the best way to do it but it was the only I can create consistent results.

😃