cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Calculate difference between two date/time values

Hi,

I'm new to Power BI data modelling, and still trying to figure out how to make measures and calculations

In this case, what I need is to have several measures that can calculate the following things:

Income Actuals / Revenue Actuals

Income Acct Plan / Revenue Acct Plan

Income Acct Plan 2 / Revenue Acct Plan 2

Is there any way to do this calculations between data in different rows? I think there may be some way using a measure with filters?

I've also tried to transpose this table, but then I have the same problem with the columns of Variance Acct Plan % (which are Variance Acct Plan / Acct Plan).

Thanks for the help!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX

## Re: Calculate difference between two date/time values

@NaCingolani - Always helpful to have sample data in text to copy and paste. What I think that you want to do is pivot/unpivot your data so that you have something like:

Category,Type,Value

Sales,Actuals,7118

Sales,Acct Plan,7952

Sales,Acct Plan2, 8729

Revenue,Actuals,6089

Revenue,Acct Plan,5875

Revenue,Acct Plan2,6561

...

The you could create a matrix for what you are doing now but you should be able to easily write formulas like:

```Measure =
VAR __salesActuals = MAXX(FILTER(ALL('Table'),[Category]="Sales" && [Type]="Actuals"),[Value])
VAR __revActuals = MAXX(FILTER(ALL('Table'),[Category]="Revenue" && [Type]="Actuals"),[Value])
RETURN
__salesActuals / __revActuals```

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

4 REPLIES 4
Highlighted
Super User IX

## Re: Calculate difference between two date/time values

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Regular Visitor

## Re: Calculate difference between two date/time values

Hi Greg,

It's sample data. It's from the excel I'm using and importing to Power Bi, already scrubbed.

I've read the guidelines, thought I was following them 🙂

Highlighted
Super User IX

## Re: Calculate difference between two date/time values

@NaCingolani - Always helpful to have sample data in text to copy and paste. What I think that you want to do is pivot/unpivot your data so that you have something like:

Category,Type,Value

Sales,Actuals,7118

Sales,Acct Plan,7952

Sales,Acct Plan2, 8729

Revenue,Actuals,6089

Revenue,Acct Plan,5875

Revenue,Acct Plan2,6561

...

The you could create a matrix for what you are doing now but you should be able to easily write formulas like:

```Measure =
VAR __salesActuals = MAXX(FILTER(ALL('Table'),[Category]="Sales" && [Type]="Actuals"),[Value])
VAR __revActuals = MAXX(FILTER(ALL('Table'),[Category]="Revenue" && [Type]="Actuals"),[Value])
RETURN
__salesActuals / __revActuals```

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Resident Rockstar

## Re: Calculate difference between two date/time values

Hi @NaCingolani,

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share the data sample which we can copy and test and your desired output so that we could help further on it.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors
Top Kudoed Authors