cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Create a table based on a date

I have the following problem.

Create a measure :

NewTableLastDate =
var
Find =SWITCH(
WEEKDAY(TODAY(),2)
,1,today()-4
,7,today()-3
,6,today()-2
,today()-1
)
Var compare = if(
Find > max('tbl'[Dates])
,max('tbl'[Dates])
,Find
)
return compare

then a create a table:

TodayDat = filter('tbl','tbl'[Dates]=[NewTableLastDate])
But the filter is not working like this. if I do it like this, then it works.
NewTableLastDate =
SWITCH(
WEEKDAY(TODAY(),2)
,1,today()-4
,7,today()-3
,6,today()-2
,today()-1
)
What am I doing wrong?
1 ACCEPTED SOLUTION
Frequent Visitor

I have found the solution,

TodayDat =
VAR Find =SWITCH(
WEEKDAY(NOW(),2)
,1,today()-4
,7,today()-3
,6,today()-2
,today()-1
)
VAR Compare=if(
Find > max('tbl'[Dates])
,max('tbl'[Dates])
,Find
)
RETURN
CALCULATETABLE (
FILTER ( tbl, 'tbl'[Dates]=Compare))
4 REPLIES 4
Community Support

Hi @Slaper ,

What Greg_Deckler said makes sense.

In DAX, measures are designed to aggregate data and return a single value.

The problem arises from trying to compare the column directly to the measure in the function. You should use calculated columns to achieve the desired results.

Replace NewTableLastDate with a calculated column so that you can use this column directly in filter expressions.

I would be grateful if you could provide me with sample data, please remove any sensitive data in advance.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Frequent Visitor

I have found the solution,

TodayDat =
VAR Find =SWITCH(
WEEKDAY(NOW(),2)
,1,today()-4
,7,today()-3
,6,today()-2
,today()-1
)
VAR Compare=if(
Find > max('tbl'[Dates])
,max('tbl'[Dates])
,Find
)
RETURN
CALCULATETABLE (
FILTER ( tbl, 'tbl'[Dates]=Compare))
Frequent Visitor

Sorry, how to upload a .pbix

Super User

@Slaper You can't use measures like that. Measures are for use at the reporting level.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors