Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
I have a paginated report that has a relative date parameter: Previous Month, Previous Quarter, YTD, FYTD.
All of them are filtering correctly except YTD. YTD is also returning FYTD data. Is there a way for it to search and only return those that match the exact parameter? I will include my current query below
The [Date_Period_Combine] field is a merged column of each date period it belongs too.
I added the parameter within the query designer using the CONTAINS operator and this is the code that is added to query
DEFINE VAR vProductSalesDatePeriodCombine1 = IF(PATHLENGTH(@ProductSalesDatePeriodCombine) = 1, IF(@ProductSalesDatePeriodCombine <> "", @ProductSalesDatePeriodCombine, BLANK()), IF(PATHITEM(@ProductSalesDatePeriodCombine, 2) <> "", PATHITEM(@ProductSalesDatePeriodCombine, 2), BLANK()))
VAR vProductSalesDatePeriodCombine1ALL = PATHLENGTH(@ProductSalesDatePeriodCombine) > 1 && PATHITEM(@ProductSalesDatePeriodCombine, 1, 1) < 1
EVALUATE SUMMARIZECOLUMNS('Product Sales'[Manufacturer], 'Product Sales'[Sold By Group], 'Product Sales'[Sold By], 'Product Sales'[Account Manager], 'Product Sales'[Company], 'Product Sales'[RoomReady Cost], 'Product Sales'[Partner Cost], 'Product Sales'[Total Cost], 'Product Sales'[Month Year], 'Product Sales'[Year Quarter], 'Product Sales'[Date Period Combine], 'Product Sales'[MIN Date Entered], 'Product Sales'[Max Date Entered], RSCustomDaxFilter(@ProductSalesManufacturer,EqualToCondition,[Product Sales].[Manufacturer],String), FILTER(VALUES('Product Sales'[Date Period Combine]), (vProductSalesDatePeriodCombine1ALL || IF(vProductSalesDatePeriodCombine1 <> "", FIND(vProductSalesDatePeriodCombine1, 'Product Sales'[Date Period Combine], 1, 0) > 0, TRUE))))
Within the last FILTER expression, can I tell it to search for the exact matching so FYTD is not returned with YTD
FILTER(VALUES('Product Sales'[Date Period Combine]), (vProductSalesDatePeriodCombine1ALL || IF(vProductSalesDatePeriodCombine1 <> "", SEARCH(vProductSalesDatePeriodCombine1, 'Product Sales'[Date Period Combine], 1, 0) > 0, TRUE)))).
I am trying to avoid changing the text YTD to "Year to Date" to make this work. I appreciate your help and time!