Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Help!! Store output of a measure

Hi,

I have the following table call [Table]:

7.PNG

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]:

 

8.PNG

 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):

 

9.PNG

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.

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

 

😃 

View solution in original post

2 REPLIES 2
UKNSI-Powerbi
Frequent Visitor

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/24Week 2405           28/01/24 03/02/24Week 2406           04/02/24 10/02/24Week 2407           11/02/24 17/02/24
PartDescriptionTot StockDemandSupplyNetDemandSupplyNetDemandSupplyNetDemandSupplyNetDemandSupplyNet
R0042001599BANANAS4820744040000877675478 82289680020000954899072 8641713608 72809
R0042001619APPLES1360758160015149  15149720 14429400 14029312 13717
R0042001649PEARS201931868 183252200120002812513868000347393150 315893780 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
PartDescriptionTot StockDemandSupplyNetDemandSupplyNet
R0042001599BANANAS4820744040000877679072 86417

 

Anonymous
Not applicable

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.

 

😃 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors