Calculating sum of YTD Net Revenue for last period

[wojpie]

Hi,

I'm struggling with the following issue: I'm trying to calculate sum of YTD Revenue for all projects for the last period. I have table Tab1 with 3 columns: PROJECT_ID, PERIOD, YTDNetRevenue. Measure for the YTD Net Revenue is as follows:

NetRevenue = SUM(Tab1[YTDNetRevenue])

Since I want to calculate sum of YTD Net Revenues for all projects (PROJECT_ID) for the last period, I'm using the following formula:

YTDNetRevenueLast =
CALCULATE (
    [NetRevenue],
    LASTNONBLANK (
        Tab1[Period],
        1
    )
)

Now I want to show the sum YTD Net Revenue for all projects for the last period in the current year, let's say - May. The problem appears when a project was completed earlier in the year, for example in March - there won't be any records for this PROJECT_ID in Tab1 for April and May. Hence, when I put PROJECT_ID and YTDNetRevenueLast in the table, the sum for the column is correct, but the Total is incorrect - it shows sum of values for those IDs which have a record in the most recent period - May. And I want to have in the Total the sum of all revenues, no matter whether the project is completed or not.

I tried different approaches, but nothing worked. I would be really grateful if you could help me.

Regards  

[v-shex-msft]

HI @wojpie,

Current measure not works properly on total level if it contains some specific filters.

For this scenario, you need to add condition to check current row and write particular formula for total level calculation.

BTW, measure also has issue to calculation with summarized records on total level, it not calculated on summarized records, it will calculated to each rows and summary these result.

Reference links:

Cumulative Total

Order of Evaluation in CALCULATE Parameters

Regards,

Xiaoxin Sheng