Date Filter

[HT123]

Hello everybody,

my report in Power BI Desktop currently consists of 2 excel sheets that are linked by a relationship (m:n) with double-sided cross-filter direction. The common criterion is the “order number”.

Table 1 contains the columns “Order Number”, “Occupancy Time” and “Date”.

Table 2 contains the columns “Order Number”, “Quantity” and “Date”

In addition, the column with the date is divided into a Hirachie -> year month day If I want to display in a report the sum of the amount with the sum of the occupancy time per month in the form of a bar chart and additionally want to provide the user with a filter for selecting the month, I would have the two columns “Date” of Table 1 and Table 2 merge in the background. is a merging of the two date columns in the background somehow possible, so that only one filter can be used????  Alternatively, I would have to show in the report two individual filters, which must be selected in each case with the same month by the user.

[calerof]

Hi @HT123 ,

Make sure you have a date table. 

Here's a video from Guy in a Cube about it.

Regards,

Fernando

[HT123]

HI Fernando,

I don't think I explained my problem properly. Here again the actual situation presented wisely

Here are my 2 Excel Sheets, the relation and the visual

The bar chart is intended to show the sum per month. The quantity is correctly displayed. Unfortunately, time is wrong. There should be a time a month of 4 coming out not 20! Is this somehow possible??
I would still need a relation from Table 1 Column "Date" to Table 2 Column "Date."

[calerof]

Hi @HT123 ,

I replicated your sample data, created a date table, and created a dim table for orders like so:

I did not create a relationship between table A & B on Orders, instead I created a relationship of each table with the dim order table above.

I created a simple measure in each table:

Total Qty = SUM('Table A'[Quantity])

Total Time = SUM('Table B'[Time])

This is the result:

Hope it helps!

Regards,

Fernando

[calerof · Accepted solution]

Hi @HT123 ,

Here are the data & pbix sample files.

Data sample

pbix

Cheers,

Fernando

[HT123]

Hi @calerof ,

Thanks for your quick help. I would have thought that there would be no solution to this.

The community is a great thing.