Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
jtownsend21
Responsive Resident
Responsive Resident

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: 
OfficePeriod

Revenue

11/1/201955000
12/1/201970000
13/1/2019 
21/1/201990000
22/1/201980000
23/1/2019 
  • Invoice detail - revenue deatailed information for past and present. 
    • Sample Data: 
      OfficeInvoice IdDate

      Amount

      1252/1/201950000
      1222/1/20195000
      1272/12/20197000
      1282/15/201923000
      1303/1/201910000
      1353/5/201915000
      1403/10/201910000
      2242/2/201925000
      2232/2/201960000
      2212/26/201920000
      2293/8/201975000
      2323/9/201975000
      2333/25/201950000
      1191/5/201925000
      1171/10/201925000
      1161/15/201910000
      2181/6/201925000
      2151/25/201945000
      2201/25/20195000

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: 

Reconciliation Example.PNG 

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

2 ACCEPTED SOLUTIONS
v-lili6-msft
Community Support
Community Support

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

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

View solution in original post

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. 

View solution in original post

5 REPLIES 5
v-lili6-msft
Community Support
Community Support

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

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

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 

 

Sample PBIX

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. 

Anonymous
Not applicable

Can you post some same data or the pbix file?

Anonymous
Not applicable

Here's the final matrix, I think it's close to what you had in mind:

Final Table.png

Steps:

  1. 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 )
  2. After that table is loaded, create the following measures:
  3. 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:

https://1drv.ms/u/s!Amqd8ArUSwDS0Fmgie21PMQRNcKM

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.