Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sanimesa
Post Prodigy
Post Prodigy

FIRSTDATE and LASTDATE functions broken?

Has anything changed with the Firstdate and Lastdate functions in the May release?

 

 

I have a date slicer with the invoice date added to it. To display the slicer selection, I use the below measure:

 

Invoice Date Selected = "Reporting Period: " & FORMAT(FIRSTDATE(InvoiceTable[InvoiceDate]), "MMMM DD, YYYY") & " - " & FORMAT(LASTDATE(Reporting_JSG_ConsolidatedOrders[InvoiceDate]), "MMMM DD, YYYY")

 

It used to work before but now I get an error:

A date column containing duplicate dates was specified in the call to function 'FIRSTDATE'. This is not supported.

 

 

 

2 ACCEPTED SOLUTIONS
v-shex-msft
Community Support
Community Support

HI @sanimesa,

 

I'm not so sure why firstdate and lastdate function not works on your side. (I test with last version without any issue)

Can you please share pbix file to test?


In addition, you can also try to use below formula to instead:

Invoice Date Selected =
"Reporting Period: "
    & FORMAT (
        MINX ( ALLSELECTED ( InvoiceTable[InvoiceDate] ), [InvoiceDate] ),
        "MMMM DD, YYYY"
    )
    & " - "
    & FORMAT (
        MAXX (
            ALLSELECTED ( Reporting_JSG_ConsolidatedOrders[InvoiceDate] ),
            [InvoiceDate]
        ),
        "MMMM DD, YYYY"
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

HI @sanimesa,

 

After checking your pbix file, I found it similar as @KHorseman said, 'firstdate' and 'lastdate' function not works if your date table contains duplicate records.


I modify my formula and current it can works on your scenario, please take a look at below formula:

Invoice Date Selected =
"Reporting Period: "
    & FORMAT (
        MINX ( VALUES ( Sheet2[InvoiceDate] ), [InvoiceDate] ),
        "MMMM DD, YYYY"
    )
    & " - "
    & FORMAT (
        MAXX ( VALUES ( Sheet2[InvoiceDate] ), [InvoiceDate] ),
        "MMMM DD, YYYY"
    )

15.gif

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

8 REPLIES 8
v-shex-msft
Community Support
Community Support

HI @sanimesa,

 

I'm not so sure why firstdate and lastdate function not works on your side. (I test with last version without any issue)

Can you please share pbix file to test?


In addition, you can also try to use below formula to instead:

Invoice Date Selected =
"Reporting Period: "
    & FORMAT (
        MINX ( ALLSELECTED ( InvoiceTable[InvoiceDate] ), [InvoiceDate] ),
        "MMMM DD, YYYY"
    )
    & " - "
    & FORMAT (
        MAXX (
            ALLSELECTED ( Reporting_JSG_ConsolidatedOrders[InvoiceDate] ),
            [InvoiceDate]
        ),
        "MMMM DD, YYYY"
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft

 

Uploaded a sample below:

https://1drv.ms/u/s!AqfAbaFIezosba5FPnh8QNoUwxM

 

This contains two examples, one set works, the other does not. It is probably data related but can't figure out why. 

@v-shex-msft Just wondering if you had a chance to look at this. 

The error message is pretty clear. You can't use a column that contains the same date value multiple times in these functions. They're meant to be used on a date dimension table. If you're using a fact table where the same date can be repeated more than once, you should use MIN and MAX instead.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman I have duplicate dates in both sets in my example, one is working while the other is not. Additionally, this used to work before on the same dataset. I guess I will try and use some other method but I have a feeling the May upgrade caused it. 

HI @sanimesa,

 

After checking your pbix file, I found it similar as @KHorseman said, 'firstdate' and 'lastdate' function not works if your date table contains duplicate records.


I modify my formula and current it can works on your scenario, please take a look at below formula:

Invoice Date Selected =
"Reporting Period: "
    & FORMAT (
        MINX ( VALUES ( Sheet2[InvoiceDate] ), [InvoiceDate] ),
        "MMMM DD, YYYY"
    )
    & " - "
    & FORMAT (
        MAXX ( VALUES ( Sheet2[InvoiceDate] ), [InvoiceDate] ),
        "MMMM DD, YYYY"
    )

15.gif

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Sometimes FIRSTDATE and LASTDATE will appear to "work" on non-contiguous non-unique date columns if the dates selected just happen to lack overlaps that affect the calculation, or in some cases the error is small enough that you won't notice it. The fact that you've used them in the past just means you accidentally got away with it, not that it was the correct way to use them.

 

Now, if you were to connect all of this to a date dimension and point those FIRSTDATE and LASTDATE formulas at that table's date column with a relationship to this table's date column, they should work as expected. If you want to stick with the table structure you're already using, use some variation of MIN/MAX/MINX/MAXX instead.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




#Confidence Range = CALCULATE(SELECTEDVALUE(MLPredictionsX[Confidence Range net]),LASTDATE(MLlnput[ProdDate]))
 
In this I am not used the Min and Max in place of LastDate

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.