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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Raif8522
Frequent Visitor

Existing or New Record Dax Formula

Hi Everyone,

 

i have got a small issue when i am trying to find records which are new or existing when filtering with date slicer dynamically.

My data table is like below.

on "HasRecordOneYearAgo" column, i checked if the productId has a record going back a year ago, if has then Existing Record and if no New Record. 

However a record can be both new or existing because the DAX formula goes row by row and checks if there is a record for the product exactly 12 months ago.

 

thats why i used Maxx formula but no luck. My date filter is dynamic from the slicer and its between 21/10/2024-19/10/2025. 

So if a record previously have sales between 21/10/2023-19/10/2024 it means Existing if not then new but formula below goes by date on each row so it can be both new and existong which it doesnt give correct sales for new items and existing items.

any help will be appreciated.

TA

 

HasRecordOneYearAgo = 
VAR CurrentDate = Table[Date]
VAR OneYearAgoDate = DATE(YEAR(CurrentDate) - 1, MONTH(CurrentDate), DAY(CurrentDate))
VAR RecordID = Table[ProductID]
RETURN
VAR PreviousRecords = 
    COUNTROWS(
        FILTER(
            Table,Table[ProductID] = RecordID &&
            Table[Date] = OneYearAgoDate
        )
    )
VAR IsInDateRange = 
    CALCULATE(
        COUNTROWS(Table),
        Table[Date] >= MIN(DimDateImpact[Date]) &&
        Table[Date] <= MAX(DimDateImpact[Date])
    )
RETURN
MAXX(Filter(Table,Table[ProductID]=Table[ProductID] && Table[Date] >= MIN(DimDateImpact[Date]) &&
        Table[GamingDate] <= MAX(DimDateImpact[Date])),IF(
    IsInDateRange > 0,
    IF(PreviousRecords > 0, "Existing Record", "New Record"),
    BLANK())

)

 

 

Raif8522_0-1738588197915.png

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi,

Thanks for the solution bhanu_gautam  offered, and i want to offer some more infotmation for user to refer to.

hello @Raif8522 , based on your descriotion, you have date slicer, and you want to the result change with the date slicer, it is better that use the measure instead of calculated column, the calculated column will not change by your slicer, you can refer to the following sample.

Sample data 

vxinruzhumsft_0-1738649806207.png

And there is a calendar table and have 1:n relationship between tables.

You can create a measure.

MEASURE =
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[ProductID] ),
        DATEADD ( 'DimDateImpact'[Date], -1, YEAR )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Date] ) IN VALUES ( 'DimDateImpact'[Date] ),
        IF ( a > 0, "Existing", "New" )
    )

Then create a table visual, and put the following field of the data table to it.

vxinruzhumsft_1-1738650023576.png

Then create a slicer ,and put the date column of calendar table to it, then fileter.

Output

vxinruzhumsft_2-1738650082917.png

 

Best Regards!

Yolo Zhu

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

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi,

Thanks for the solution bhanu_gautam  offered, and i want to offer some more infotmation for user to refer to.

hello @Raif8522 , based on your descriotion, you have date slicer, and you want to the result change with the date slicer, it is better that use the measure instead of calculated column, the calculated column will not change by your slicer, you can refer to the following sample.

Sample data 

vxinruzhumsft_0-1738649806207.png

And there is a calendar table and have 1:n relationship between tables.

You can create a measure.

MEASURE =
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[ProductID] ),
        DATEADD ( 'DimDateImpact'[Date], -1, YEAR )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[Date] ) IN VALUES ( 'DimDateImpact'[Date] ),
        IF ( a > 0, "Existing", "New" )
    )

Then create a table visual, and put the following field of the data table to it.

vxinruzhumsft_1-1738650023576.png

Then create a slicer ,and put the date column of calendar table to it, then fileter.

Output

vxinruzhumsft_2-1738650082917.png

 

Best Regards!

Yolo Zhu

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

 

bhanu_gautam
Super User
Super User

@Raif8522 , Try using

 

dax
HasRecordOneYearAgo =
VAR CurrentDate = Table[Date]
VAR OneYearAgoStartDate = DATE(YEAR(CurrentDate) - 1, MONTH(MIN(DimDateImpact[Date])), DAY(MIN(DimDateImpact[Date])))
VAR OneYearAgoEndDate = DATE(YEAR(CurrentDate) - 1, MONTH(MAX(DimDateImpact[Date])), DAY(MAX(DimDateImpact[Date])))
VAR RecordID = Table[ProductID]
RETURN
VAR PreviousRecords =
CALCULATE(
COUNTROWS(Table),
Table[ProductID] = RecordID &&
Table[Date] >= OneYearAgoStartDate &&
Table[Date] <= OneYearAgoEndDate
)
RETURN
IF(
PreviousRecords > 0,
"Existing Record",
"New Record"
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Hi @bhanu_gautam ,

 

Thanks for your reply and DAX.

i tried and this time the result set is just giving all of them as a New Record. 

Any ideas why is this happening?

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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