The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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())
)
Solved! Go to Solution.
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
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.
Then create a slicer ,and put the date column of calendar table to it, then fileter.
Output
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.
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
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.
Then create a slicer ,and put the date column of calendar table to it, then fileter.
Output
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.
@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"
)
Proud to be a Super User! |
|
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?