Table is not filtering on Dates

[JB0007]

Hi Power BI community,

I have created a manual date table using the DAX formula:

Dates = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year",YEAR([Date]),
    "Quarter No", QUARTER([Date]),
    "Quarter", "Q"&QUARTER([Date]),
    "Month No",MONTH([Date]),
    "Month",FORMAT([Date], "MMM"),
    "Day",DAY([Date]),
    "Date_Full",DATE(YEAR([Date]),MONTH([Date]),DAY([Date]))
)

I have imported a table from SharePoint were other dates are present.

I want to filter the SharePoint list based on the date selected in the custom table, but I am not having any result:

Can someone please help me,

Any help will be much appreciated.

[amitchandak]

@ , Not able to join dots. Can you explain what is needed and the logic behind that?

see if this can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

[JB0007]

Hi @amitchandak , thank you for your feedback.

I have removed the EndDate column because it is irrelevant.

But lets say, I want to filter the first table based on the date selected from the second table.

I can use the dates from the same table (first table) but I cannot customize the date as I wanted e.x.
I want to set Today as the first date in the table and then sort by in descending order.

I can only achieve this using a Custom Date Table (which is the second table).
However, when a select for example 25 Feb, it is not filtering the table.

[Anonymous · Accepted solution]

Hi @JB0007 ,

Firstly, you could change the second table to slicer visual.

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers 

Secondly, the [start date] in the first table is datetime type so it will not It is not equal to the date selected in the second table -- 8:00 am <> 12:00 am. I suggest you to change them to date type for comparison.

At last, you could create a measure to rank the [start date]. Set the selected date as 0 and sort dates order by measure.

_start = SELECTEDVALUE(Table1[start date])

rank_ = IF(SELECTEDVALUE(Table1[start date])=SELECTEDVALUE(Table2[date]),0,RANKX(all(Table1),[_start],,ASC))

Best Regards,

Jay