topic Re: Date filtering in DAX Commands and Tips

Date filtering

Anonymous — Mon, 07 Mar 2022 16:50:25 GMT

Hi!

I'm relatively new to Power Bi and was having trouble with implementing a date filter.

The task is to have a date filter that only show data that falls before but NOT including the selcted date. The before feature in the fiter date widget shows values on the selected date.

Is there a DAX command that can overide the filter and exclude values on the selected date?

Anything helps, thank you!!

Re: Date filtering

Jihwan_Kim — Mon, 07 Mar 2022 17:53:09 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file.

One of ways to solve this is to have disconnected calendar table.

Sales total: = CALCULATE ( SUM ( Data[Sales] ), FILTER ( 'Calendar', 'Calendar'[Date] < MAX ( 'Calendar slicer'[Date] ) ) )

Re: Date filtering

Anonymous — Mon, 07 Mar 2022 20:42:22 GMT

Thank you for your help!
I just had a query, for the "Sales Total:" DAX command, how would that be altered if there wasn't anything to sum up. For example, if the chart was composed of peoples' name, date of birth or other values that are unable to be summed?

Re: Date filtering

Jihwan_Kim — Tue, 08 Mar 2022 04:07:15 GMT

Hi,

Thank you for your feedback.

I think you can use other function, instead of SUM, for instance, selectedvalue / min / max / and so forth.

Please share your sample pbix file, and then more accurate solution can be written.

Thanks.

Re: Date filtering

Anonymous — Tue, 08 Mar 2022 16:57:02 GMT

Hi,

I attached a sample file. I am having trouble replicating the solution you made.

I appreciate the help, let me know if you have trouble accessing anything.

Thank you!

https://drive.google.com/file/d/1gNgU38EX6l7EDIuCryQm9bVyBxobe-Z8/view?usp=sharing

Re: Date filtering

Jihwan_Kim — Tue, 08 Mar 2022 17:15:37 GMT

Hi,

Please check the attached file.

I changed a relationship type.

Name measure: = CALCULATE ( SELECTEDVALUE ( data[name] ), FILTER ( data, data[date] < MAX ( 'Calendar'[date] ) ) )

Re: Date filtering

Anonymous — Fri, 25 Mar 2022 18:14:47 GMT

Hi,

I was wondering how I would alter this measure to exclude the people who leave (have a status of -1) but include everyone with a status of 1 and only show the people before but not including the selected date?
I attached a sample power bi file.
https://drive.google.com/file/d/1gyRlYRIZ0KckVOvFBFm6s2tGEq8MvpHM/view?usp=sharing

Thank you!