cancel
Showing results for
Did you mean:
Highlighted
Member

## 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 1 3/1/2019 2 1/1/2019 90000 2 2/1/2019 80000 2 3/1/2019
• Invoice detail - revenue deatailed information for past and present.
• Sample Data:
 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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team

## Re: Relative Date Function - IF Past do 'X', IF Present do 'Y'

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

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.
Member

## Re: Relative Date Function - IF Past do 'X', IF Present do 'Y'

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]
)
)```

5 REPLIES 5
New Contributor

## Re: Relative Date Function - IF Past do 'X', IF Present do 'Y'

Can you post some same data or the pbix file?

New Contributor

## Re: Relative Date Function - IF Past do 'X', IF Present do 'Y'

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

Community Support Team

## Re: Relative Date Function - IF Past do 'X', IF Present do 'Y'

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

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.
Member

## Re: Relative Date Function - IF Past do 'X', IF Present do 'Y'

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

Member

## Re: Relative Date Function - IF Past do 'X', IF Present do 'Y'

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]
)
)```