Calculate difference between two date/time values

[Anonymous]

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!

[Greg_Deckler]

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

[Anonymous]

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 🙂

[v-piga-msft]

Hi @Anonymous,

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

[Greg_Deckler · Accepted solution]

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