cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Matrix

I have a table:

1/31/2019 --- 5.00  --- Washington

1/31/2019 --- 6.00 --- California

1/31/2019 --- 7.00 --- Oregon

1/31/2019 --- 10.00 --- Mexico

3/31/2019 --- 6.00  --- Washington

3/31/2019 --- 7.00 --- California

3/31/2019 --- 8.00 --- Oregon

3/31/2019 --- 9.00 --- Mexico

12/31/2019 --- 8.00  --- Washington

12/31/2019 --- 9.00 --- California

12/31/2019 --- 10.00 --- Oregon

12/31/2019 --- 11.00 --- Mexico

And i am trying to get the variance (YearEnd-MonthEnd) and show it in a matrix.  Would you be able to assist?

Here is how it should look like:

1/31/2019               12/31/2019

Washington                      3                                 0

California                          3                                 0

Oregon                             3                                 0

Mexico                             3                                  0

I created a new query and tried to merge and append the 2 queries but the matrix ends up summarizing the yearend data which is not correct.   Thank you for your help.

3 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team

## Re: Matrix

Hi @Anonymous ,

To create a measure as below.

```Measure 2 =
CALCULATE (
SUM ( 'Table'[time] ),
FILTER ( ALL ( 'Table' ), 'Table'[date] = ENDOFYEAR ( 'Table'[date] ) ),
VALUES ( 'Table'[City] )
)
- CALCULATE ( SUM ( 'Table'[time] ) )
```

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Hi

Anonymous
Not applicable

## Re: Matrix

SUM(GoalVariance[ShareBalance])/CALCULATE( SUM (GoalVariance[GoalValue]), FILTER (ALL (GoalVariance), GoalVariance[MonthEndDate] = ENDOFYEAR (GoalVariance[MonthEndDate])), VALUES (GoalVariance[AcctBranchName]))

I cannot thank you enough.  This is the last piece i am working on before taking a 3 week vacation on the 4th!!!  Thank you thank you thank you again

Glen
3 REPLIES 3
Community Support Team

## Re: Matrix

Hi @Anonymous ,

To create a measure as below.

```Measure 2 =
CALCULATE (
SUM ( 'Table'[time] ),
FILTER ( ALL ( 'Table' ), 'Table'[date] = ENDOFYEAR ( 'Table'[date] ) ),
VALUES ( 'Table'[City] )
)
- CALCULATE ( SUM ( 'Table'[time] ) )
```

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Hi

Anonymous
Not applicable

## Re: Matrix

SUM(GoalVariance[ShareBalance])/CALCULATE( SUM (GoalVariance[GoalValue]), FILTER (ALL (GoalVariance), GoalVariance[MonthEndDate] = ENDOFYEAR (GoalVariance[MonthEndDate])), VALUES (GoalVariance[AcctBranchName]))

I cannot thank you enough.  This is the last piece i am working on before taking a 3 week vacation on the 4th!!!  Thank you thank you thank you again

Glen

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors