Live Connection - Dates Confusion

[Staregirl]

Hi all,

Working on Live Connection for the first time. Having trouble with anything date related.

The measure below, on its own works perfectly well to give me the sum of 'Active' premiums on a specified date between two dates - PolicyEffectiveDate and PolicyExpirationDate - they are in the same table:

Active Premium := CALCULATE (
[TotalPremium],
FILTER (Policy, (Policy[PolicyEffectiveDate] <= MAX ('Date' [Date])) && (Policy[PolicyExpirationDate] > MIN ('Date' [Date]))))

[TotalPremium] is a simple sum of a column - Sum(PolicyLine[Premium]) - from a fact table 

HOWEVER Policy[PolicyEffectiveDate] and it's partner are not contiguous dates.

There are two related 'PolicyEffective/ExpirationDate' tables and an unrelated 'Date'Date table.

All of these are contiguous and run from 01/01/1900 to 31/12/9999

If I try to feed the ActivePremium measure into a measure that uses any time series function, I get an error: "An invalid numeric representation of a date value was encountered"

For instance:

Active Premium at EOP Last Year :=
CALCULATE (
[ActivePremium],
FILTER( ALL ('Date' [Date]), DATEADD ('Date' [Date], -1, YEAR)
))

I think this is because the dates are not contiguous? 

I am trying to achieve something like this:

Active Premiums With Date Relationship :=
VAR PeD = TREATAS (VALUES(Policy[PolicyEffectiveDate]), 'Date'[Date]
VAR PexD = TREATAS (VALUES(Policy[PolicyExpirationDate]) 'Date'[Date]
VAR Result = CALCULATE (
[TotalPremium],
FILTER(Policy,
Ped <= MAX ('Date' [Date]) &&
PexD > MIN('Date' [Date])))
RETURN
Result

But I can't get the syntax right.

It returns some numbers, but not the same numbers as [ActivePremiums] which is what I was expecting. Also, using TREATAS seems to affect all the visuals on the page, even ones that aren't using that measure.

Can anyone help?

I can post a snip of the model but it's quite large.

Thanks!

[v-stephen-msft]

Hi @Staregirl ,

Please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

From now on, this simliar post might be helpful to solve your problem:

https://community.powerbi.com/t5/Desktop/Error-with-SAMEPERIODLASTYEAR-an-invalid-numeric-representa...

Best Regards,

Stephen Tao

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

[Staregirl · Accepted solution]

As I mentioned, I am working over a Live Connection so Time Series information is not applicable here.

I solved this by using RELATED instead of TREATAS. So I used RELATED to filter my fact table by the contiguous PolicyEffectiveDate, instead of using TREATAS to try to connect the 'Date' table to the PolicyEffectiveDate.

Happy to provide more info here for anyone with the same sort of issues.

[amitchandak]

@Staregirl , Try like

Active Premium := CALCULATE (
[TotalPremium],
FILTER (Policy, (Policy[PolicyEffectiveDate] <= MAX ('Date' [Date]) && Policy[PolicyEffectiveDate] <> date(1900,01,01) ) && (Policy[PolicyExpirationDate] > MIN ('Date' [Date])
|| Policy[PolicyExpirationDate] = date(9999,12,31))))

[Staregirl]

Hi Amit

I tried using your measure instead but I'm afraid I still get "An invalid numeric representation of a date value was encountered"

Thanks for trying, though 🙂