Forum Discussion
Helper IIHow 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:
9 Replies
- Greg_Deckler
Community Champion
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
JuanBolasGreg_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.
- Greg_Deckler
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.
- JuanBolas
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
- Greg_Deckler
JuanBolas Yeah, I was totally guess and flying blind without sample data.
- JuanBolas
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!
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.
- JuanBolas
helloy Anonymous.
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])returnCALCULATE([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.
