How to write Dax Formula for Date Filter in power bi

[Charu]

Hi Everyone

Need help ASAP

The Requirement is I have one date field and In date filter, I would like to display the Today date or Now() date but that should display all the data from the excel.

Based on date selection related data  should display

For Example :

1.Invoice_date field I will use in date Filter

2.Display date should be Today date ie) current date

3.Should display all data in filter I mean data up to till date and then based on user  selection data will vary

Is there any options there to write such formulas.

Thanks in advance!

[v-ljerr-msft · Accepted solution]

Hi @Charu,

If I understand you correctly, you should be able to follow steps below to get the expected result.

1. Use the formula below to create a new calculate table.

Date = CALENDAR(MIN(Table1[INVOICE_DATE]),TODAY())

2. Create a relationship between the new created Date table and your fact table.

3. Use the Date column from the Date table as Slicer on the report.

Note: You'll need to refresh your dataset at least daily to make the dates in Date table up to date.

Here is the sample pbix file for your reference. 

Regards

[Charu]

Hi @v-ljerr-msft

Thanks a ton for your support. 

This is what my expected result.

[v-ljerr-msft]

Hi @Charu,

Could you post your table structures with some sample/data, and the expected result against the data? So that we can better assist to write the DAX formula for you. 

Regards

[Charu]

Sample Data

Invoice_No       INVOICE_DATE                               Entity                      Amount
1001                  12/15/2010(MM/DD/YYYY)             SVStradeltd              50000
1002                  09/15/2009(MM/DD/YYYY)             SVStradeltd              30000
1003                 12/15/2008(MM/DD/YYYY)              SVStradeltd              250000
1005                 10/25/2007                                       SVStradeltd              84520

Date Filter Box By default "Current DATE" for example(01/16/2018) and In visual data should be

Invoice_No       INVOICE_DATE                               Entity                      Amount
1001                  12/15/2010(MM/DD/YYYY)             SVStradeltd              50000
1002                  09/15/2009(MM/DD/YYYY)             SVStradeltd              30000
1003                 12/15/2008(MM/DD/YYYY)              SVStradeltd              250000
1005                 10/25/2007                                       SVStradeltd              84520

then When I select 12/15/2010 then the result is as below

Invoice_No       INVOICE_DATE                               Entity                      Amount
1001                  12/15/2010(MM/DD/YYYY)             SVStradeltd              50000