cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
NaCingolani Frequent Visitor
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? 

 

DataSet1.PNG

 

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


4 REPLIES 4
Super User
Super User

Re: Calculate difference between two date/time values

Is that sample data output or source data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


NaCingolani Frequent Visitor
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 Smiley Happy

Super User
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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

Re: Calculate difference between two date/time values

Hi @NaCingolani,

 

Have you solved your problem?

 

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.