Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Renco
Frequent Visitor

Filter table dynamicaly on dates

Hello community,

 

I'm trying to get a table (or measure) which is filtered through dates.

What I want to achieve is to filter the table on "begindatum" (which is starting date in English) and I only want to see the records which are currently active.

For example: today is the second of November, so what I need is all records which are active right now.

- I don't want to see the prices starting at the first of December

- I don't want to see the prices from the first of October if there is a price which starts at the first of November.

- I also want to filter the table on "Verkoopscode" RRPN.

 

This is just an example, but my real table is a lot larger than this. 

 

Table CustomerPriceGroup.jpg

 
Basiccaly what I'm trying to do is creating a table where only one of each articlenumber is present and which contains the starting date closest to today (=today or earlier than today).

 

Thanks in advance for your help.

 

Regards,

Renco

1 ACCEPTED SOLUTION


@Renco wrote:

With an OData connection or any other 'import' connection this works like a charm. However with a DirectQuery connection this solution is not possible.

 

So if someone knows another way to achieve this goal, suitable for a DirectQuery connection, I'm looking forward to hearing it.

 

Thanks in advance.


@Renco

In DirectQuery mode, say if the data source is SQL Server, you can create the columns in SQL instead of in Power BI desktop. Then you can still apply filter to the true/false column.

View solution in original post

5 REPLIES 5
Renco
Frequent Visitor

This works:

 

 

New Column in modelling tab:

 

Datum = CALCULATE(LASTDATE(Klantenprijsgroep_Sheet[Begindatum]);ALLEXCEPT(Klantenprijsgroep_Sheet;Klantenprijsgroep_Sheet[Artikelnr.]);Klantenprijsgroep_Sheet[Begindatum]<=TODAY())

 

With another column:

 

Rapportage = IF(Klantenprijsgroep_Sheet[Begindatum]=Klantenprijsgroep_Sheet[Datum];true();false())

 

And filtering in the visual itself on 'true'.

 

Perhaps there is a better way than this but at least it's a solution

The solution is elegant, for me, I'll take the same solution. 

With an OData connection or any other 'import' connection this works like a charm. However with a DirectQuery connection this solution is not possible.

 

So if someone knows another way to achieve this goal, suitable for a DirectQuery connection, I'm looking forward to hearing it.

 

Thanks in advance.


@Renco wrote:

With an OData connection or any other 'import' connection this works like a charm. However with a DirectQuery connection this solution is not possible.

 

So if someone knows another way to achieve this goal, suitable for a DirectQuery connection, I'm looking forward to hearing it.

 

Thanks in advance.


@Renco

In DirectQuery mode, say if the data source is SQL Server, you can create the columns in SQL instead of in Power BI desktop. Then you can still apply filter to the true/false column.

@Eric_Zhang

Thanks for your reply. I am familiar with this possibility. Currenty we do it like this. However I'm looking for a way that doesn't involve editting the SQL database. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.