Do some calculations based on the max date record for every month

[Anonymous]

Hello Everyone!

I have a small question: I have a revenue table with all the current and past months/years. There is a column where it is indicated when the records were populated in the mentioned table, just like a "refresh date" column. Im trying to calculate the sum of the  revenue column, filtered by a certain type of product (column product = 'product A'). But, I'd like to grab the records only for the last/MAX refresh date of each month, that means that if in January we had 3 "refresh dates" (01/01; 01/10; 01/31), i want to sum the revenue only for the records of the last refresh date in January (01/31) and for product A (2 filters being applied). And I would like to do that for every month of every year.

Any tips on how to achieve that?

Thanks a lot!

[krohitrao]

Hello,

I see the solution to this in the following way:
1. Create a MonthYear column in the revenue table based on RefreshDate column
2. Create a key RecordKey = Product+MonthYear in the revenue table

3. Create a summarized table: Group by RecordKey, MAX(RefreshDate)

4. Create a column in Revenue table as LOOKUP for each RecordKey and bring MaxRefreshDate = MAX(RefreshDate) from the summarized table

5. Create another column MaxRefreshDateFlag = if MaxRefreshDate = RefreshDate then 1 else 0

6. Use this flag to get data only for max refresh dates for each product and the correspnding date.

Please let know if this helps.

[v-rongtiep-msft]

Hi @Anonymous ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _MONTH =
    MONTH ( MAX ( 'Table'[date] ) )
VAR _YEAR =
    YEAR ( MAX ( 'Table'[date] ) )
VAR _MACDATE =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[subject] = SELECTEDVALUE ( 'Table'[subject] )
                && MONTH ( MAX ( 'Table'[date] ) ) = _MONTH
                && YEAR ( MAX ( 'Table'[date] ) ) = _YEAR
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[VALUE] ),
        FILTER ( ALL ( 'Table' ), 'Table'[date] = _MACDATE )
    )

If I have misunderstood your meaning, please provide more details with your desired output and more sample data.

Best Regards

Community Support Team _ Polly

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

[Anonymous]

Hello @v-rongtiep-msft ,

Thank you for all the support. But the final goal seems to be easier than i expected earlier. When the revenue list comes is, it overwrites the existent one for that month. So i just need to calculate the revenue for each month based on 2 other column filters and keep all the remaining filter context. What it is really challenging me now is when i don't have revenue info for the actual month, i need to use the previous month revenue as the value for the actual, and i am quite confused on how to solve it

Thank you!

[v-rongtiep-msft]

Hi @Anonymous ,

I can't get exactly what you mean, please provide a simple sample of the data and the desired output(Preferably in image form, like the sample I provide to you).

Best Regards

Community Support Team _ Polly

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

[Ashish_Mathur]

Hi,

To your visual, drag Year and Month name from the Calendar Table.  In your slicer, select a certain product.  Write these measures

Total revenue = sum(Data[Revenue])

Last date till which revenue is available = max(Data[Date])

Revenue as on the last date = calculate([Revenue],datesbetween(calendar[date],[Last date till which revenue is available],[Last date till which revenue is available]))

Hope this helps.

[ribisht17]

@Anonymous 

Please refer Solved: Calculate new value for max date of each month - Microsoft Power BI Community

Regards,

Ritesh

[amitchandak]

@Anonymous , lastnonblankvalue can help

lastnonblankvalue([Refresh Date], sum(Table[Revenue]))

You can use allexcept to remove some other filter in context

calculate(lastnonblankvalue([Refresh Date], sum(Table[Revenue])) , allexcept (Table, Table[Product], Table[Month Year]))

[Anonymous]

Hello!

What if i'd like to increment to the actual month the value of the previous month if the actual month revenue has not been refreshed yet?

I'd like to get all the previous month the same, but if the actual month hasnt been populated with revenue's info yet, i would like to input to the measure the latest value available

Thank you