Forum Discussion
Relative Date Function - IF Past do 'X', IF Present do 'Y'
- 7 years ago
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
- 7 years ago
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
- jtownsend217 years agoResponsive Resident
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.