Repeat quaterly results for every month of that quarter

[RPAI2812]

Hi,

I have some data of granularity 2, which I have information of quaterly results for each row.

I wanted the quaterly results to be repeated for every month of that quarter for each vessel and drill. In other words, I wanted the blank cells of table below to be filled with their reference of results for the quarter (that is referenced to a random date inside the quarter).

How is the best way to do that?

[amitchandak]

@RPAI2812 , if you have date with help from Time intelligence and date table

Qtr Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER('Date'[Date])))

[RPAI2812]

Hi! Thanks for the response. I do use the time intelligence functions and my date table is set as date table.
However, it didn't seem to work.. let me give some more details.

In power query, my data consists only of one date per quarter, with the quarterly results of that specific [drill] and [vessel]. I just needed the values from 31/03/2021 to be repeated for a new row of 28/02/2021 and 31/03/2021, for instance. 

[v-chenwuz-msft]

Hi @RPAI2812 ,

Is that you want to get the end date of each quarter?

DAX code:

EndofQuarter = DATE(YEAR([Date]),QUARTER([Date])*3+1,1)-1

M code:

EndofQuarter = Date.EndOfQuarter([Date])

If I have misunderstood you, share some sample data and desired results.

Best Regards

Community Support Team _ chenwu zhu

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

[RPAI2812]

Hi!

You misunderstood. I wanted something like below.
I have a table with some results from Unit 1 or 2 and Activity 1 or 2 per quarter. This result is in a row with the last day of the quarter. I want to create a new table with additional rows of the other months of each quarter and that the result from the quarter is repeated in these new rows.

[v-chenwuz-msft · Accepted solution]

Hi @RPAI2812 ,

Maybe you can try this code:

Result from original table =
CALCULATE(
    FIRSTNONBLANK( 'Original Table'[Result], 0 ),
    FILTER(
        'Original Table',
        [Quarter] = EARLIER( 'Repeated Table'[Quarter] )
            && [Unit] = EARLIER( 'Repeated Table'[Unit] )
            && [Activity] = EARLIER( 'Repeated Table'[Activity] )
    )
)

result :

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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