DAX date issue - earliest date and record

[Jsummersgill86]

Hi,

I've been out of day to day Power BI building for a couple of years but a project has come across my desk which should be pretty quick and easy. I have a very small data model, with a central fact table, a dimension table and a date table. My fact and dimension table are sourced from a SQL view and table respectively.

My fact table contains revenue estimates for office locations by state. The OfficeID is related to OfficeID in the dimension table. When a new estimate is entered a new record is inserted with an Est_dt corresponding to the date of the estimate. Est_dt is related to DATE in my date table. At each Est_dt I have an amount for both the cumulative revenue estimate and also the difference in revenue estimate from the prior estimate. An example of my fact table is below.

On my Power BI report page I have visuals and a relative date slicer (using DATE field from my Date table) such that I can filter for something like "revenue estimates entered in last 2 months" to see what estimates have been entered for offices recently. In this visual I am trying to present the following for each office:

• A date representing the most recent estimate date. I have the following DAX already:

Most Recent Date = LASTDATE(FACT[Est_Dt])

• A value representing the total revenue estimate at the end of the date period filtered. For this I have the following DAX which appears to be working:

CALCULATE(sum(Est_rev), topn(1, ALLSELECTED(FACT[Est_Num]),CALCULATE(max(FACT[Est_Num])),desc))

• A date representing the very estimate date. I have the following DAX already that appears to be working but seems to be more complicated than I think it should be:

First release date = CALCULATE( FIRSTDATE( FACT[Est_Dt]), FILTER(all(DIM_DATE[Date]),SELECTEDVALUE(FACT[OfficeId])))

• A value representing the revenue estimate at the beginning of period filtered by the date (effectively a brought forward balance)

• A value representing the very first estimate made for that office (i.e. an estimate that have been made longer than 2 months ago). This I am really struggling with - I am thinking that for this I need some combination of CALCULATE and ALL to get the Est_Rev on the earliest date. I have the following DAX already written which works when my table visual is filtered for a specific OfficeID, but runs into a resource issue when only filtered by date.

ITD_Estimated Insurance Payment_First =

CALCULATE([ITD_Estimated Insurance Payment],FILTER(all(DIM_DATE[Date]),DIM_DATE[Date]=[First release date]))

Effectively the below is what I'd want to achieve:

As I say, been out of the loop for a while and this feels like I"m very much over complicating the issue so hoping someone can help!

[v-dineshya]

Hi @Jsummersgill86 ,

Thank you for reaching out to the Microsoft Community Forum.

I have done some changes in your DAX  measures. In additionally i have created "Initial Estimate Value", "Prior Estimate Value", "Current Estimate Value" and "Change in Estimate" measures with sample syntax.

1.

Most Recent Date =
CALCULATE(
MAX(FACT[Est_Dt]),
ALLEXCEPT(FACT, FACT[OfficeID])
)

Note: It calculates per OfficeID within the filter context.

2.

First Release Date =
CALCULATE(
MIN(FACT[Est_Dt]),
ALLEXCEPT(FACT, FACT[OfficeID])
)

3.

Initial Estimate Value =
CALCULATE(
SUM(FACT[Est_rev]),
FILTER(
ALL(FACT),
FACT[Est_Dt] = CALCULATE(MIN(FACT[Est_Dt]), ALLEXCEPT(FACT, FACT[OfficeID]))
)
)

4. Assume your Date table is used in slicers, and slicer is filtering for last 2 months, you can use below measure

Prior Estimate Value =
VAR StartDate =
MIN(DIM_DATE[Date])
VAR PriorDate =
CALCULATE(
MAX(FACT[Est_Dt]),
FILTER(
ALL(DIM_DATE),
DIM_DATE[Date] < StartDate
),
ALLEXCEPT(FACT, FACT[OfficeID])
)
RETURN
CALCULATE(
SUM(FACT[Est_rev]),
FILTER(
ALL(FACT),
FACT[Est_Dt] = PriorDate
)
)

5.

Current Estimate Value =
VAR MaxEstNum =
CALCULATE(
MAX(FACT[Est_Num]),
ALLEXCEPT(FACT, FACT[OfficeID])
)
RETURN
CALCULATE(
SUM(FACT[Est_rev]),
FILTER(
FACT,
FACT[Est_Num] = MaxEstNum
)
)

6.

Change in Estimate = [Current Estimate Value] - [Prior Estimate Value]

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @Jsummersgill86 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @Jsummersgill86 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[Jsummersgill86 · Accepted solution]

Hi Dinesh, I've managed to resolve this issue. I actually ended up reworking the data model a little bit which I meant I could leverage some of the out of the box time intelligence functions a bit more easily.

Thanks

[v-dineshya]

Hi @Jsummersgill86 ,

If your issue is resolved, Please share the details here and mark it as 'Accept as solution' to assist others with similar issues. If it did not, please provide further details.

Regards,

Dinesh

[v-dineshya]

Hi @Jsummersgill86 ,

If your issue is resolved, Please share the details here and mark it as 'Accept as solution' to assist others with similar issues. If it did not, please provide further details.

Regards,

Dinesh

[Greg_Deckler]

@Jsummersgill86 Since you have Office ID in the visual, your First release date should just be:

First release date = MIN( 'DIM_DATE'[Date] )

Then your ITD_Estimated Insurance Payment_First should be:

ITD_Estimated Insurance Payment_First =
  VAR __Table = FILTER( 'Fact Table', [Date] = [First release date] )
  VAR __Result = SUMX( __Table, [Est_rev] )
RETURN
  __Result

[Jsummersgill86]

Thanks for the response Greg.

Given that my visual is filtered with a relative date slicer wouldn't MIN('DIM_DATE'[Date]) return the earliest date per the slicer? Rather than the earliest date from the fact table for each specific OfficeID?

[Greg_Deckler]

@Jsummersgill86 Ah, misunderstand that ask. That would be:

First release date = MINX( ALL( 'DIM_DATE' ), [Date] )

[Jsummersgill86]

I think I'm getting even more confused than I was at the start. When I used your proposed 

First release date = MINX( ALL( 'DIM_DATE' ), [Date] )

My table visual gets blown out when I add this to it. For those Rev estimates occuring in my slicer date range I have a release date equal to the very first date in my Date table (not the first date for that specific office ID estimate). My visual then also then starts including all other office IDs (even if they haven't had an estimate in the slicer range).