cancel
Showing results for
Did you mean:
Helper I

## The cost value is to be displayed on a monthly basis based on a start date and a time period

I would like to show cost values month-wise on a matrix or chart based on a start date and duration.

The data I have are as follows

 PROJECTQUOTESID PHASEID STARTMONTH ENDMONTH QUOTEPRICE PERIOD AverageQuotePrice 21 195081 2022-11-01 2023-01-31 39999 3 13333 22 195081 2022-10-17 2023-04-30 150010 7 21430 24 195081 2022-12-01 2023-01-01 55000 2 27500 25 195081 2023-01-01 2023-02-28 125670 2 62835 26 195081 2023-02-01 2023-02-28 5000 1 5000 44 195081 2022-10-11 2022-10-25 4680 1 4680 45 195081 2022-10-24 2022-10-27 580 1 580 47 195081 2022-12-12 2023-01-31 500 2 250

And I am looking for a measure that is showing the AverageQuotePrice month-wise beginning on the STARTMONTH and the following month taking the period into consideration.

Example

 PROJECTQUOTESID PHASEID STARTMONTH ENDMONTH QUOTEPRICE PERIOD AverageQuotePrice 21 195081 2022-11-01 2023-01-31 39999 3 13333 22 195081 2022-10-17 2023-04-30 150010 7 21430

I would like to see

PROJECTQUOTEID 21 average value 13333 on Nov. 22, Dec. 22 and Jan. 23

PROJECTQUOTEID 22 average value 21430 on Oct. 22, Nov. 22, Dec. 22, Jan. 23, Feb. 23, Mar. 23  and Apr. 23

when using the measure on a matrix or chart with a date column or axis

Can anyone help me with a measure of getting the result, thanks a lot.

1 ACCEPTED SOLUTION
Helper I
4 REPLIES 4
Helper I
Community Support

Hi @Typhoon74 ,

Based on your description, is the result you expect: when a certain id is selected, then the corresponding value from the start date to the end date pair is dynamically displayed on the column? Also you only provide the value corresponding to the average month, how should the value corresponding to each specific month be reflected?
Can you provide the relevant screenshot information and describe it.

Best Regards,
Henry

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

Helper I

Hi @v-henryk-mstf,
the result I am looking for is to see from the example ID21 and ID22 as per the screenshot below that I prepared manually.

The average value of ID 21 should be displayed for 3 months starting November

The starting month is November and the period is 3 does mean the value on Nov. 22, Dec. 22, Jan. 23

The average value of ID 22 should be displayed for 7 months starting October

The starting month is October and the period is 7 does mean the value on Oct. 22, Nov. 22, Dec. 22, Jan. 23, Feb. 23, Mar. 23, Apr. 23

Does this make sense to you?

In a matrix, it should look like this

On a chart, I am looking for something like this

Helper I

Hi @v-henryk-mstf do you have any suggestions?

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors