cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JuanBolas
Helper II
Helper II

How to merge two DAX formulas

Hello,

 

I have two DAX formulas that I need to merge into one.  The objective is to get last month's revenue, costs and profit/loss.  I have an ISStructure table joined to an Actual's table and then a measure (Actual Total Consolidate) which calculates a running total starting with revenue.   Total Costs and Profit/Loss are not in the actuals table.

 

What I have now on one side is:

Ingresos Totales = calculate([Total Actual Consolidate],ISStructure[Concepto]="TOTAL DE INGRESOS")
 
Which gets the Revenue (Total de Ingresos) for the corresponding line in an Income Statement structure.
 
On the other hand I have:
 
This Month =
var _max = eomonth(today(),0)
var _min = eomonth(_max,-2)+1
return
    CALCULATE([Total Actual Consolidate],
    DATESBETWEEN('DateTable'[Date],_min,_max))
 
I would like them to be a single formula.
 
 
Thanks in advance,
 
9 REPLIES 9
v-polly-msft
Community Support
Community Support

Hi @JuanBolas ,

What is your desired output ? I am still confused.

How to Get Your Question Answered Quickly 

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

helloy @v-polly-msft.

 

I need to set up three cards: Month's Total Revenue, Month's Total Costs, and Month's Total Profit/Loss, something like so:

 

Ingresos Totales = calculate([Total Actual Consolidate],ISStructure[Concepto]="TOTAL DE INGRESOS")

 

But this gives me the total for the sum of any periods shown and I only want the last month with data.

 

The other part of the equation would be to get the data for the corresponding month:

 

This Month =
var LastMonthWIthData = Max('Actual'[MonthYear])
return
    CALCULATE([Total Actual Consolidate],'Actual'[MonthYear]'=LastMonthWithData)

 

But that gets me nowhere and I can't think of how to combine the two in a single equation for the card.

 

The output I'm looking for is the value of Revenue ('Ingresos Totales") for the current month (the max date of the dates in the Date ('MonthYear') column in the 'Actual' table.

 

These values are the result of a combination of measures which in essence do a running table of the amounts and I take the corresponing line item.

 

I get the data into the matrix visual by referencing the an Income Statement structure table and the actual and budget values from separate tables.

 

My PBIX is here: https://drive.google.com/file/d/1pThZIX9B0IpD-PNVngHB-B3xA48p8HRv/view?usp=sharing

 

Thanks in advance.

JuanBolas
Helper II
Helper II

Thanks @Greg_Deckler, but something's off.

The ISStructure is the Income Statement structure (the line items that it consists of) and is joined to both the Actual table (which has the actual data) and the budget table.  I have a report that has a matrix and that displays actual vs. budget for each ISStructure row, doing a running total of both the actual and budget columns so as to have the Net Revenue, EBITDA, EBIT, and Profit and Loss.  Since Net Revenue (line Ingresos Brutos), EBITDA, EBIT and Profit/Loss (Resultado Neto) are not not included in any of the tables I haven't found a way to access the their value for a specific period.

 

Here is the PBIX you requested: https://drive.google.com/file/d/1pThZIX9B0IpD-PNVngHB-B3xA48p8HRv/view?usp=sharing 

 

Thanks in advance

 

@JuanBolas Yeah, I was totally guess and flying blind without sample data.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

Sorry about that.  I thought I had posted the link to my PBIX in the original post.

 

Any idea on how to solve this?

 

Thanks in advance!

Sorry about that.  I thought I had posted the link to my PBIX in the original post.

 

Any idea on how to solve this?

 

Thanks in advance!

Greg_Deckler
Super User
Super User

@JuanBolas Not sure what you are trying to do here exactly.

Ingresos Totales = calculate([Total Actual Consolidate],ISStructure[Concepto]="TOTAL DE INGRESOS")
 
Which gets the Revenue (Total de Ingresos) for the corresponding line in an Income Statement structure.
 
On the other hand I have:
 
This Month =
var _max = eomonth(today(),0)
var _min = eomonth(_max,-2)+1
var _IngresosTotales = calculate([Total Actual Consolidate],ISStructure[Concepto]="TOTAL DE INGRESOS")
var _someothervar = 
    CALCULATE([Total Actual Consolidate],
    DATESBETWEEN('DateTable'[Date],_min,_max))
RETURN
  IF(<some condition>,_IngresosTotales,_someothervar) // no idea this is psuedo-code

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler sorry I was not clear.

 

What I'm trying to acheive are three separate measures to put in cards that will display Revenue (Total de Ingresos), Total Costs (Costos Totales) and Profit/Loss (Resultado Neto) for the month corresponding to the last date of data in the Actuals Table (in this case August) (MAX(Actual[MonthYear])).  Based on achieving that I want to do calculate the same for the previous month.

 

I am able to generate a measure that displays the total of the line item (say, Revenue) for the period sliced but not just the total for the month in which last data was entered.  Separately, in another measure I can display data for a date range but it shows me the entire column, not the line item. 

 

Is that clearer?

 

Thanks in advance. 

 

 

@JuanBolas OK, that makes it clearer. So, you should be able to do something like:

Last Month Entered =
  VAR __MaxDate = MAXX(ALL('ISStructure'),[Date]) //assuming this table is where you figure out the last data entered
  VAR __MinDate = DATE(YEAR(__MaxDate),MONTH(__MaxDate),1) //first of the month
RETURN
  CALCULATE([Total Actual Consolidate],FILTER('ISStructure',Date] >= __MinDate && [Date] <= __MaxDate))

This is a best guess because I don't know your measure formula for Total Actual Consolidate and that forces me to use CALCULATE which I would prefer not to, stay away from TI functions and I also don't have sample data and expected results.

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.