The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
I have 2 tables that contain revenue information.
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 |
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 |
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
Solved! Go to Solution.
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/547907
If 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.
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/547907
If 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.
Can you post some same data or the pbix file?
Here's the final matrix, I think it's close to what you had in mind:
Steps:
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:
User | Count |
---|---|
158 | |
109 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |