strange behavior of firstdate / lastdate

[laskawiec]

Hi Everyone

I'm looking for a solution in time intelligence related calculations. Basically the situation is this. We have a product that is paid for each month(we call it a "flatfee"). There is a set date where the payments start and if the product is no longer in offer there is a set date where the monthly payments stop.

Now I'm trying to calculate the total payout, and for that to work I'm using a metric. The problematic part is if the payment start date is after the selected period (via calendar table slicer).

So let's suppose I set the relative date slicer to Last calendar year (01-Jan-18 to 31-Dec-18).

My formula is | (datediff(tempfirstdate, templastdate, month) +1) * flatfee | and i have problems with the lastdate part.

the lastdate formula is:

templastdate = if(lastdate(All_Rooms[dateFlatFeeStart]) > lastdate('Calendar'[Data]), lastdate(All_days_by_Rooms[dateFlatFeeStart].[Date]), lastdate('Calendar'[Data]))

Now if dateFlatFeeStart > lastdate(calendar), i get 31-Dec-19 AND I HAVE NO IDEA WHY!

Please see the table with examples below. I have provided temporary fields in the table showing both lastdate(All_Rooms[dateFlatFeeStart]) and lastdate('Calendar'[Data]) and neither of those dates are what i get as the result of the templastdate metric.

See below

Thanks in advance for all your help

Cheers,

Filip

[v-frfei-msft]

Hi @laskawiec ,

As the oneline document of LASTDATE function. It Returns the last date in the current context for the specified column of dates. So in your table visual, it will return the last date of the curent row.

BTW, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[v-frfei-msft · Accepted solution]

Hi @laskawiec ,

To update your measure as below.

OwnerFlatfeeRevenueNetto = 
SUM ( 'Room data'[FlatFee] )
    * IF (
        LASTDATE ( 'Calendar'[Date] ) < FIRSTDATE ( 'Room data'[Date FlatFeeStart] ),
        BLANK (),
        DATEDIFF (
            IF (
                LASTDATE ( 'Room data'[Date FlatFeeStart] ) > LASTDATE ( 'Calendar'[Date] ),
                IF (
                    ISBLANK ( LASTDATE ( 'Room data'[Date Unavailable] ) ),
                    LASTDATE ( 'Calendar'[Date] ),
                    LASTDATE ( 'Room data'[Date Unavailable] )
                ),
                IF (
                    FIRSTDATE ( 'Calendar'[Date] ) > LASTDATE ( 'Room data'[Date FlatFeeStart] ),
                    FIRSTDATE ( 'Calendar'[Date] ),
                    LASTDATE ( 'Room data'[Date FlatFeeStart] )
                )
            ),
            IF (
                ISBLANK ( LASTDATE ( 'Room data'[Date Unavailable] ) ),
                IF (
                    LASTDATE ( 'Room data'[Date FlatFeeStart] ) > LASTDATE ( 'Calendar'[Date] ),
                    LASTDATE ( 'Room data'[Date FlatFeeStart].[Date] ),
                    LASTDATE ( 'Calendar'[Date] )
                ),
                LASTDATE ( 'Room data'[Date Unavailable] )
            ),
            MONTH
        ) + 1
    )

[laskawiec]

Thank you so much Frank

Honestly i thought my formula was complicated as it was and had a suspicion that I might be overcomplicating it. Anyway thanks so much - I've been scratching my head over this for the last few days.

Cheers

Filip

[laskawiec]

Hi Frank

Thanks for participating in the discussion

https://1drv.ms/u/s!AqV4-uym-o-IbUo_h1GCCtqkIZM?e=emk5PM

Here i have created a mockup data sample and a simple model explaining the situation.

Expected result is: OwnerFlatfeeRevenueNetto = flatfee * number of months where the product is active (after DateFlatFeeStart and before DateUnavailable)

eg. if date slicer set to 1/1/2018 - 12/31/2018 all products should have 0 (this doesnt work)

if date slicer set to 1/1/2019 - 9/1/2019 then: (this works properly)

product 1 = 900

product 2 = 450

product 3 = 1400

product 4 = 520

product 5 = 1800

So basically my problem is that the formula doesnt understand what should happen in the past (no payment)

Thanks for your engagement in this topic

F