Calculating based on summaries outside the current report context

[Anonymous]

I am working to duplicate a report in Power BI that a client currently produces via a complex and time-consuming Excel sheet.

I'm running up against some things that are simple to do in Excel but that are vexing me in Power BI.

For each time context for which they produce/view the report, there is a row for every consecutive day. The report counts how many contact records have values in selected date columns matching each of those days (e.g, on May 11, here's how many contacts had Event A happen, how many had Event B happen, and so on). I've got that part working, it seems.

1. The first problem is that the Excel sheet starts with a row that contains the counts carried over from the *previous* report. In other words, it's a summary of how many "Event A's," "Event B's," and so on are left over from the previous reporting context. So I'm summarizing non-numeric data within the report, but apparently I also need to summarize it *outside* the current report context--and to do so dynamically depending on the time frame they're looking at.

2. The second problem is that the Excel report also provides the cumulative counts. For example, if there are 347 "Event A's" carried over from the previous report, and 23 more "Event A's" on May 11, then the cumulative "Event A" count as of May 11 is 370. If there are 15 more "Event A's" on May 12, the cumulative "Event A" count on that day is 385. The report should show the cumulative number as of each day for each of these event counts.

So it seems to me that for this part I'm not so much summarizing the data in the underlying tables so much as I am summarizing the summaries that are within the table visualization on the report view. 

Again, this part would be simple in Excel (this isn't what makes the Excel so time-consuming). I'm sure there must be a way to do it in Power BI, but I'm stumped as to what that way might be. 

I can't create or change any queries in the underlying database, but I have full access to do anything I want to the Power BI data model. Any help you can provide would be wonderful. Thank you!

[Anonymous]

Here is an illustration of the Excel sheet I am trying to duplicate (with identifying info obfuscated):

They run this report every day within each of the "periods" (they have a few of those per year). The "Carry Over" (Row 😎 is the cumulative number as of the previous "period," and within this report each day's number gets added to that total to make the cumulative number in columns F, L, N, and P. Events B and C (Columns G through J) don't rely on the Carry Over for their cumulative totals.

So these numbers are counts of records in the contacts table that have date values in the fields for Event A, Event B, and so on. As such, these numbers only exist within the context of the summarizing done on this report. So how would I determine the Carry Over total to put onto my Power BI report?

Right now the Excel sheet reads the Carry Over value from several other sheets in the Excel workbook, where those values appear to be manually input. This is part of the inefficiency I'm looking to fix for our client using Power BI. But how to tell Power BI how to obtain these Carry Over numbers is what has me vexed. I hope this helps to clarify the issue I'm having. 

Thanks for looking into this with me!

[v-yuta-msft]

Hi leandros,

What does "previous context" mean? Does it mean a slicer based on a date column or other?

If it is , so your requirement is to achieve the aggregation value based the date value you have selected in your slicer and categorized by event like a running total, right? In this senario, you can use DAX function such as this pattern: 

result =
CALCULATE (
    aggregation,
    ALLSELECTED ( table[date] ),
    ALLEXCEPT ( table, table[event] )
)

In addtion, could you please clarify more details about your requirement and expect result>

Regards,

Jimmy Tao

[Stachu]

could you share examples of th edata tables you're using? how is the previous report differentiated from current?