topic Re: How to create a new measure for dates in DAX Commands and Tips

How to create a new measure for dates

Yul — Fri, 02 Sep 2022 23:25:31 GMT

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 ?

Re: How to create a new measure for dates

daXtreme — Sat, 03 Sep 2022 08:09:28 GMT

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?

Re: How to create a new measure for dates

Whitewater100 — Sat, 03 Sep 2022 12:59:41 GMT

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!

Re: How to create a new measure for dates

Anonymous — Tue, 06 Sep 2022 05:48:54 GMT

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