Slicing a table by Month and Year From Same Date Table

[dexter00000]

Hi,

I have a date table and another table. Both have YEAR,MONTH columns. But because there is only 1 active connection i can only slice YEAR or MONTH.I don't want to multiply the date table to use 2 different slicers. I want to be able to slice them separately with 2 slicers by only 1 date table. How can i achieve this?

Thanks, 

[dexter00000]

so this is how it looks with additional columns. 

I only have 1 dates table and no other dimension tables year will slice the fact table and month will too they can be used together. But also separately too. Is this achievable?

[lbendlin]

You don't have a Dates table yet. The Date column is missing. For Time Intelligence functions to work that date column must be contiguous (no gaps) and covering (including the result of the time intelligence).

Please show a sanitized version of your data model.

[dexter00000]

I didn't include them in the picture but it's like this.

[dexter00000]

AY,DÖNEM are the same thing. Messing around got those a bit confusing. Basically AY,DÖNEM(MONTH) and YIL(YEAR) will be sliced by the date table. But i don't know how will i achieve it with only 1 connection.

[lbendlin]

Your Query2 is not marked as a Dates table. 

In your Query1 add a surrogate column with the first day of the year and month of your fact data.  Link both tables via that column Query2 1:* Query1, single direction.

[dexter00000]

---

@lbendlin wrote:

first day of the year and month of your fact data.  Link both tables via that column Query2 1:* Query1, single direction.

---

The way you described it i couldn't understand. First day of the year 1.01.2022 alright, and month? another column or not another column, February or  1.02.2022? I'm not sure could you describe it a bit more clearly.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[dexter00000]

Hi again,

Okay i made a pbix file for it in onedrive and again got some screenshots for what's desired.

So basically month slicer can slice when it's Date Table.Month Name:*-Fact Table.Month:*

But can not slice by year.

Again with Date Table.Year:*-Fact Table.Year:* can slice the fact table by year but not by month.

 

What's desired is that i need to make a connection that can separately slice by both but at the same time it should work together too.

By what i've understand i'm supposed to use the time intelligence Date column but i can't do it by my Fact Table.Year or Fact Table.Month. So how can i achieve this?

Here is the onedrive link: datetableexample.pbix

[lbendlin · Accepted solution]

see attached

[dexter00000]

Okay, this is what i was trying to achieve thank you so much!

Now i understand what you meant by adding first date of each month.

I'm gonna look into time intelligence more from now on. Used to do month and year slices by the year and month columns only.

Thank you so much again,

Have a good day.

[dexter00000]

I'm sorry if i'm a bit slow on this. Do you mean a single column with 1.01.2022,1.02.2022,1.03,2022,...,1.01.2023,1.02.2023,...,1.01.2024,1.02.2024 values? And after i do this column i'll link it to time intelligence(Date) column? I could use a visual or an example on this.

[lbendlin]

Add a derived date to your fact table (for example first day of the month) and link that to your date table.

[dexter00000]

Hi i'm not sure how will that work. I tried first day of the month to first day of the month way,i tried Date Hierarchy to first day of the month. But none of them work. Am i supposed to have only 1 connection? YEAR and MONTH will be 2 different slicers that are in the same table.

[lbendlin]

If you want these slicers to work independently then you need separate year and month dimension tables that control the Dates table. If they should interact, use the year and month columns from the Dates table. Do not base the slicer on the fact table .