Forum Discussion

jtownsend21's avatar
jtownsend21
Icon for Responsive Resident rankResponsive 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 ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    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. 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Can you post some same data or the pbix file?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

    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