Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

one page filter for two different dates

I have 2 visualisations on the same page. these visualisations are based on 2 different tables / each visualisation is based on its own, specific table.  both visualisations contain a date field/co...
Need Help
Kaviraj11's avatar
Kaviraj11
Solution Sage
2 years ago

Hi,

 

I would suggest you to create Date/Calendar Table and there are multiple ways to create them. 

Create date tables in Power BI Desktop - Power BI | Microsoft Learn

 

Here’s a simple DAX formula to create a basic date table:

DateTable = CALENDAR(DATE(2023, 1, 1), DATE(2023, 12, 31))

For a more advanced date table that includes additional columns like month names, day of the week, and so forth, you can use the following template:

Date = 
VAR MinYear = YEAR(MIN(Sales[Order Date]))
VAR MaxYear = YEAR(MAX(Sales[Order Date]))
RETURN
ADDCOLUMNS(
    FILTER(
        CALENDARAUTO(),
        AND(
            YEAR([Date]) >= MinYear,
            YEAR([Date]) <= MaxYear
        )
    ),
    "Calendar Year", "CY " & YEAR([Date]),
    "Month Name", FORMAT([Date], "mmmm"),
    "Month Number", MONTH([Date])
)

 

Then you can create a relationship as Many to One from both the tables using Date Columns to Date Table. Additionally, you will use the date column from Date Table in a Slicer. It will display the output as you expect.

 

I hope I understood the request correctly.  Let me know if you any questions.

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

hi Kaviraj,
thanks for your suggestion.
I created a datefiltertable.
the datefiltertable contains a date-field.
the date-field contains all the day between a min and a max using CALENDAR().

 

I related this date-field in my model to the date-field of my visualisation's table.
but my visualisation shows year, quarter and month.

 

I can create a year-field, a quarter-field and a month-field in my datefiltertable, but I seem to have 2 problems:

1. how to avoid multiple year, quarter and month combinations in my filtertable?

2. how to relate these filtertable year-field, quarter-field and month-fields in my model to fields of my visualisation's table since only max 1 many-to-many relation is allowed between 2 tables....