1

I have the following question regarding Power Query. For the correct setup, please enable Power Pivot (in COM Add-ins in Excel):

enter image description here

Let us first consider the following example:

Table demonstrating the problem

I have inserted the text below, so one could try it in Excel themselves.

Customer Date Amount
Tim 01/01/2020 32
Tim 02/01/2020 31
Tim 02/01/2020 41
Tim 02/01/2021 52
Chris 01/01/2020 52
Chris 01/02/2021 22

To add this to our data model, we first select the range with the data and then press ALT + A + P + T. This gives us the Power Query Editor (see below):

Power Query Editor

Subsequently, we go to Load To...

Load to

and then select the following options:

Import options

Now we have our Pivot and we would like to create our measure to calculate the cumulative totals. Let's add the measure:

Measure

Measure

Now if we were to create the Pivot, we would get:

enter image description here

This is good, however I would like to get the total cumulative, instead of the YTD. However I am not sure how to edit the function such that it results in the total cumulative.

Any help on how such a function should look like is highly appreciated. It -needs- to be done using a measure, since this is in my opinion the proper approach.

I have tried using DAX or M-language, but to no avail, as I fail to understand how this should work.

Snowflake
  • 211

1 Answers1

2

Try

TotalYTD:=CALCULATE(SUM([Amount]),FILTER(ALLEXCEPT(Table1,Table1[Customer]),Table1[Date]<=MAX(Table1[Date])))

Jos Woolley
  • 541