Power BI dax | How to search data in previous date range based on a current date range.

[ankit_sobti]

I have a Table with date and names as column headers. Using Power BI, what I want is, when a user selects a particular date or date range (Quarter 2), Power BI will see the corresponding name in the column header and search in the previous quarter if the same name exists.

Example date columns, has dates for last 5 years, and name columns has corresponding names. Since many names have been repeated in names column, so I want to check for a partucular date, (say todays date), check in the last 3 months if the same name has been repeated? I have tried datesbetween function but it is not working. Can you share the syntax using Dax.

[ankit_sobti]

https://drive.google.com/drive/folders/17zPwiGD4GeRa4dWyPrlKG-3CQT-cGPgT?usp=sharing

Thank you so much for the comment, i have shared the sample file in the location above along with the sample excel file and screenshot below.

The file has 2 fields (Date and Name), Date field has values from 1 Jan 2021- 30 Oct 2021 and Names are Randomly generated as text.

I have a slicer which has a range of dates, and what I want is when a user selects the date filter with start date and end date (say October 2021 as an example here), Power BI will check into the previous month data if any name that exsits in current month is also in the previous month.

What i want is Power BI to search how many are new values in current selection that does not exist in previous month.

I want to do same with quarter, if 3 months data is selected from slicer then Power BI can check in previous 3 months for any values that are repeated.

Thanks

[v-jiewu-msft · Accepted solution]

Hi @ankit_sobti ,

I can’t open the pbix file. Based on my testing, please try the following methods:

1.Create the sample Table.

2.Drag the Date field into the slicer visual.

3.Create the new measure to filter month.

MonthValuesCount = 
VAR start_range = MIN('Table'[Date])
VAR end_range = MAX('Table'[Date])
VAR sep_start = EOMONTH(start_range, - 2) + 1
VAR sep_end = EOMONTH(end_range, - 1)
VAR _10name = CALCULATETABLE(VALUES(DateTable[Name]), FILTER(DateTable, [Date] >= start_range && [Date] <= end_range))
VAR _9name = CALCULATETABLE(VALUES(DateTable[Name]), FILTER(DateTable, [Date] >= sep_start && [Date] <= sep_end))

RETURN
//COUNTROWS(EXCEPT(_10name, _9name))
CONCATENATEX(EXCEPT(_10name, _9name), DateTable[Name])

4.Drag the measures into the card visual. The result is shown below.

Best Regards,

Wisdom Wu

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[lucadelicio]

Hi,
is better if you upload the pbix or the excel dataset.
An image that shows the result that you want.
You can use https://wetransfer.com/ to upload your file.
Thank you