cancel
Showing results for
Did you mean:
Frequent 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
Super User

## 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```

Proud to be a Datanaut!

4 REPLIES 4
Super User

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

Proud to be a Datanaut!

Highlighted
Frequent 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

Super User

## 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```

Proud to be a Datanaut!

Community Support Team

## 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.