Forum Discussion

jtownsend21's avatar
jtownsend21
Responsive Resident
7 years ago
Solved

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 ...
  • v-lili6-msft's avatar
    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/547907

     

    If not your case, please share your sample pbix file for us have a test.

     

    Best Regards,

    Lin

  • jtownsend21's avatar
    jtownsend21
    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.