Forum Discussion
Relative Date Function - IF Past do 'X', IF Present do 'Y'
I have 2 tables that contain revenue information.
- Revenue Actual - reported revenue for past months.
- Sample Data:
| Office | Period | Revenue |
| 1 | 1/1/2019 | 55000 |
| 1 | 2/1/2019 | 70000 |
| 1 | 3/1/2019 | |
| 2 | 1/1/2019 | 90000 |
| 2 | 2/1/2019 | 80000 |
| 2 | 3/1/2019 |
- Invoice detail - revenue deatailed information for past and present.
- Sample Data:
Office Invoice Id Date Amount
1 25 2/1/2019 50000 1 22 2/1/2019 5000 1 27 2/12/2019 7000 1 28 2/15/2019 23000 1 30 3/1/2019 10000 1 35 3/5/2019 15000 1 40 3/10/2019 10000 2 24 2/2/2019 25000 2 23 2/2/2019 60000 2 21 2/26/2019 20000 2 29 3/8/2019 75000 2 32 3/9/2019 75000 2 33 3/25/2019 50000 1 19 1/5/2019 25000 1 17 1/10/2019 25000 1 16 1/15/2019 10000 2 18 1/6/2019 25000 2 15 1/25/2019 45000 2 20 1/25/2019 5000
- Sample Data:
They don't line up perfectly for prior months and I need to report only the actuals + what is in for the present month.
I am use the following condition to separate past from present and reconcile the past while keeping the present.
VAR _DATE_DELTA =
MAX('Date'[FirstDayofMonth]) - DATE(YEAR(TODAY()),MONTH(TODAY()),1)
RETURN
IF(
AND(
_DATE_DELTA < 0,This works great for past and present when selected separately, however if I select both at the same time, it only sees the max date as present and does not reconcile the past. See Exmple Below:
For the example above, the correct total should say $206,200; the result of
$136,975 (actual from Feb)
+$69,225 (March Invoice Detail)
$206,200
As you can see the field "Revenue Actual + Current" has the correct amounts, but the total is off. Obviously I need to move off the MAX(Date) function, but I can't find a way to do that.
Here is a link to a Sample PBIX
hi, jtownsend21
This looks like a measure totals problem. Very common. See this post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907If not your case, please share your sample pbix file for us have a test.
Best Regards,
Lin
I found the solution. It is a 3 part solution:
First, reconcile the values from previous months:
*Reconciliation = VAR _REVENUE = SUM(Revenue[Revenue]) VAR _INVOICES = SUM(Invoice[Amount]) VAR _DATE_DELTA = MAX('Date'[FirstDayofMonth])-DATE(YEAR(TODAY()),MONTH(TODAY()),1) RETURN IF( AND( _DATE_DELTA < 0, _INVOICES = 0 ), _REVENUE, IF( AND( _DATE_DELTA < 0, NOT(ISBLANK(_REVENUE)) ), _REVENUE - _INVOICES ) )Second, add the reconciliation to the invoiced amount:
*Correct Value = SUM(Invoice[Amount])+[*Reconciliation]
Third, create a measure to total them when multiple months are selected:
*Correct Value Total = IF( HASONEVALUE('Date'[MonthYear]), [*Correct Value], SUMX( VALUES('Date'[MonthYear]), [*Correct Value] ) )Thanks for the links v-lili6-msft the answer was in there.
5 Replies
Here's the final matrix, I think it's close to what you had in mind:
Steps:
- Need a dedicated date table. There's a small function I wrote that will make a quick one. It's in the Power Query Editor. Has dates, month name, month number (important for sorting the month name correctly) and the year. Use this table to put filters on. Be it in a table, slicer, page filter, etc. This table is related to each of your fact tables (1 : M )
- After that table is loaded, create the following measures:
Total Invoice Amount = SUM( Invoice[Amount] ) Revenue Actual = SUM( Rev[Revenue] ) Previous Revenue = CALCULATE( [Revenue Actual], Filter( ALL(DimDate), MAX(DimDate[MonthNumber] ) -1 = DimDate[MonthNumber]) ) Invoice Detail, LastDate = IF( ISCROSSFILTERED( DimDate[Date]),[Total Invoice Amount], CALCULATE( [Total Invoice Amount]+ [Previous Revenue], LASTNONBLANK( DimDate[Month Name], [Total Invoice Amount]) ) )There are probably some instances that are not accounted for, but hopefully heading in the correct direction.
Here's the pbix file:
hi, jtownsend21
This looks like a measure totals problem. Very common. See this post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907If not your case, please share your sample pbix file for us have a test.
Best Regards,
Lin
Thanks v-lili6-msft. I think this is the right way to go. I tried a number of methods based on the links you shared, but still getting the same result. Below is a link to what I have attempted (I will edit the original post with the same link).
Any help is appreciated. Greg_Deckler
I found the solution. It is a 3 part solution:
First, reconcile the values from previous months:
*Reconciliation = VAR _REVENUE = SUM(Revenue[Revenue]) VAR _INVOICES = SUM(Invoice[Amount]) VAR _DATE_DELTA = MAX('Date'[FirstDayofMonth])-DATE(YEAR(TODAY()),MONTH(TODAY()),1) RETURN IF( AND( _DATE_DELTA < 0, _INVOICES = 0 ), _REVENUE, IF( AND( _DATE_DELTA < 0, NOT(ISBLANK(_REVENUE)) ), _REVENUE - _INVOICES ) )Second, add the reconciliation to the invoiced amount:
*Correct Value = SUM(Invoice[Amount])+[*Reconciliation]
Third, create a measure to total them when multiple months are selected:
*Correct Value Total = IF( HASONEVALUE('Date'[MonthYear]), [*Correct Value], SUMX( VALUES('Date'[MonthYear]), [*Correct Value] ) )Thanks for the links v-lili6-msft the answer was in there.
