Calculate 'Year to date' measure over multiple dimensions

[Anonymous]

Hi!

I have a table as described under:
-----

Date           | Location | KPI | Actual | Target
01.01.2018 | 1             | 1    | 0.94    | 1

01.02.2018 | 1             | 1    | 1.02    | 1

01.03.2018 | 1             | 1    | 0.78    | 1

...

01.01.2018 | 1             | 2    | 34812 | 30000

01.02.2018 | 1             | 2    | 38992 | 30000

...

01.01.2018 | 2             | 1    | 0.65    | 1

01.02.2018 | 2             | 1    | 0.83    | 1

01.03.2018 | 2             | 1    | 1.33    | 1

...

01.01.2018 | 2             | 2    | 55312 | 30000

01.02.2018 | 2             | 2    | 4503   | 30000
-----

As the example shows there are up to multiple Locations and KPI's. I was wondering if there is a way to write a measure to calculate the year to date values of the Actual / Target measures, seperating on Location and KPI? Is it possible in the table in its current state, or would I have to split the data by Location and KPI?

Wanted result:

-----

Date           | Location | KPI | Actual | Target | Actual YTD | Actual AVG YTD |
01.01.2018 | 1             | 1    | 0.94    | 1          | 0.94            | 0.94

01.02.2018 | 1             | 1    | 1.02    | 1          | 1.96            | 0.98

01.03.2018 | 1             | 1    | 0.78    | 1          | 2.74            | 0.91

...

01.01.2018 | 1             | 2    | 34812 | 30000  | 34812         | 34812

01.02.2018 | 1             | 2    | 38992 | 30000  | 73804         | 36902

...

01.01.2018 | 2             | 1    | 0.65    | 1          | 0.65            | 0.65

01.02.2018 | 2             | 1    | 0.83    | 1          | 1.48            | 0.74

01.03.2018 | 2             | 1    | 1.33    | 1          | 3.16            | 1.05

...

01.01.2018 | 2             | 2    | 55312 | 30000  | 55312         | 55312

01.02.2018 | 2             | 2    | 4503   | 30000  | 59815         | 29907
-----

Best regards

arefossa

[v-yuta-msft · Accepted solution]

Hi arefossa,

To achieve your requirement, please follow steps below:

Click query editor-> Add Column-> Index Column, after applied&closed, create a calculate column using DAX below:

Actual YTD = CALCULATE(SUM(Table1[Actual]), FILTER(Table1, Table1[Location] = EARLIER(Table1[Location]) && Table1[KPI] = EARLIER(Table1[KPI]) && Table1[Index] <= EARLIER(Table1[Index])))

Then create a rank column based on Location and KPI columns:

Rank = RANKX(FILTER(Table1, Table1[Location] = EARLIER(Table1[Location]) && Table1[KPI] = EARLIER(Table1[KPI])), Table1[Actual YTD], ,ASC)

Finnally, achieve the average using DAX below:

Actual AVG YTD = Table1[Actual YTD] / Table1[Rank]

 

You can also refer to the sample file in the attachment.

Regards,

Jimmy Tao

[Anonymous]

Hi @v-yuta-msft 

This worked for the current year YTD over multiple dimensions. How can you calculate the same YTD, QTD, MTD for the last year? I tried multiple functions like sameperiodlastyear or dateadd however, its not working. Could you please help. 

[Anonymous]

Hi Jimmy,

This worked for the current year YTD over multiple dimensions. How can you calculate the same YTD, QTD, MTD for the last year? I tried multiple functions like sameperiodlastyear or dateadd however, its not working. Could you please help. 

[Anonymous]

This solved my task and was exactly what I needed!

Thanks alot!

[AlB]

Hi @Anonymous

It should be feasible. If you set up your table/matrix visual as you've shown, you can use a CALCULATE with a filter argument like:

    Table1[Date]<=MAX(Table1[Date])

For instance, for the average:

Actual AVG YTD =
CALCULATE ( AVERAGE ( Table1[KPI] ), Table1[Date] <= MAX ( Table1[Date] ) )

[Anonymous]

Hi AlB!

Thanks for your reply, but there seems to be some issues with your suggestion. The [KPI] values are just Id's for the KPI dimension along with the [Location] values and not used for aggregations. The columns I wish to get the YTD and Average YTD values for are 'Actual' and 'Target'.

To clearify, I wish to get the 'Actual YTD' / 'Actual AVG YTD' for each KPI at each Location, and hopefully in the current table structure.

[AlB]

@Anonymous

Ok, so then just update the column name you are doing the calculation over, as in:

Actual AVG YTD =
CALCULATE ( AVERAGE ( Table1[Actual] ), Table1[Date] <= MAX ( Table1[Date] ) )