Creating Power BI slicer by combining 2 date/time fields from 2 different dataflows

[Lh_8900]

I would like to create a date slicer (filter) in my Power BI report. This slicer should combine 2 date/time fields from 2 different dataflows so that when we start filtering, not only 1 column (example timesheet dataflow) in the matrix visual adjusts but also the 2nd column (example leaves dataflow).

I have already tried working with a new global table 'Date' with an autogenerate date field in it. Then we created relations to our 2 different dataflows (timesheet & leaves) allowing us to use the autogenerate field in the slicer. But unfortunately, this doesn't work as some online tutorials say.

How do I solve this problem best?

[Lh_8900]

@Idrissshatila  

This is my model view as the settings of my relationships. 

Date field in table Date has the DAX formule of "Date = CALENDARAUTO()"

If I add field Date as a slicer and select for example Last 1 weeks. I have no input, table is blank. If I remove the filter then I see my values 

[Idrissshatila]

@Lh_8900 ,

so the solution for this is that go to power query and convert the data type of these columns to date instead of data time and it should work.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote for my Community Mobile App Idea 💡

[Lh_8900]

@Idrissshatila  I have changed the column 'Date' from Timesheets and 'DateFrom' from Leaves to data type 'Date' but it doesn't work with this solution. My table is still blank if I filter 😕  

Are there other things I should watch/check?

[Idrissshatila]

@Lh_8900 , did you change them from power query (transform data ) or from the data view ?

and also you should change the data type of the date column .

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote for my Community Mobile App Idea 💡

[Lh_8900]

@Idrissshatila  Only through this did I change it 

I have now changed this to 'date' in the 'transform data' view as well.
I see that the date filter works but only for timesheets and not for leave days.

Example: the person who worked 40 hours last week has 132 hours of leave. So this is not really correct, any idea where it goes wrong?

[Idrissshatila]

@Lh_8900 ,

now you should validate the data in the data view and check the numbers there, then check the measure that is getting the numbers.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote for my Community Mobile App Idea 💡

[Lh_8900]

@Idrissshatila What do you exactly mean with 'data view'? Is that the table view?

Do I understand it correctly: I need to check the date values manually to see if there is a difference or ...?

[Idrissshatila]

@Lh_8900 , yes the table view

yes check one example just to see if the data is coming right or no.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote for my Community Mobile App Idea 💡

[Lh_8900]

@Idrissshatila 

 My example:
* on 1 august Person X was on leave -> 8 hours of leave
* on 2 august, Person X was on leave for half a day & worked for half a day -> 4 hours leave + 4 hours actual hours from the timesheets 

So in my table, we should have 4 hours of actual hours and 12 hours of leave. As you can also see in the screenshots of my 'table view'. The values are correct, so there must be something wrong after this step. See next picture.

Instead I get 4 hours actual hours and 132 hours leave and so this is still not correct. 

When I look at the formulas of these fields, they have the same formula structure and it goes for sum in the visual itself. 

Something is wrong , but what the error is, I can't see directly.

Have you already faced this before?

[Idrissshatila]

Hello @Lh_8900 ,

the method of creating a global date table is the best solution, but why it didn't work in your case, what was the isseu ?

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote for my Community Mobile App Idea 💡