How to create a new measure for dates

[Yul]

Hi 

I have two tables with two different ranges of dates, I want to create a new slicer to filter both by month and year

How can I do this with a new measure ?

[Anonymous · Accepted solution]

Hi  @Yul ,

I created some data:

TableA:

TableB:

Table:

Here are the steps you can follow：

1. Create measure.

Flag =
var _selectmin=MINX(ALLSELECTED('Table'),[Date])
var _selectmax=MAXX(ALLSELECTED('Table'),[Date])
return
IF(
    AND(
       MAX('TableA'[Date])>=_selectmin,MAX('TableA'[Date])<=_selectmax)
    ||
    AND(
        MAX('TableB'[Date])>=_selectmin,MAX('TableB'[Date])<=_selectmax)
        ,1
        ,0)

2. Place [Flag] in Visual's Filter and set is=1.

3. Result:

If you need pbix, please click here.

How to create a new measure for dates.pbix

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Whitewater100]

Hi:

Assuming you do not have a continuous date table, you would need to add a date table (the month-year column) will filter both of your other tablesafter forming a relationship on the date field.You should also mark your date table as a date table. Here is some code below(table name = "Dates"  you can use to create a date table. 

MODELING>NEW TABLE> then this code:

Dates = ADDCOLUMNS ( CALENDAR (DATE(2020,01,01), TODAY()), "year", YEAR ( [Date] ), "MonthNumber", FORMAT ( [Date], "MM" ), "year-month", FORMAT ( [Date], "YYYY-MM" ), "month-year", FORMAT ( [Date], "MM-'YY" ) )

I hope this helps!

[daXtreme]

Create a new date table that will have dates from both tables and connect it to the other ones. I don't understand what you mean by "How can I do this with a new measure?." But I don't even understand why you have 2 different date tables... You should have one that stores dates that span the whole period you want to work with in any of your other tables. By the way, you should have one calendar in your model. If you have 2 tables with dates in them, then you should create a calendar and connect it to the tables. Why complicate things that can be simple?