cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gnhaines
Frequent Visitor

Defining Dates for CALCULATE using a parameter and DATESMTD

Metric Current Period = SWITCH('Period Selection'[Period Type Selected],
    1, CALCULATE(Transactions[Metric],
        DATESMTD(
        {
        [AsAtDate]
        })
    )
)
Error: Cannot find table ".

 

For the purposes of monthly reporting, I'm trying to define a range of dates to calculate a metric over. The visual will then show a single total for only that period selected. AsAtDate is a measure corresponding to the last day that the report is being made for. I think it's a problem with the way the Column/Single Column Table is being defined?

 

Can I use a Measure in this way?

3 REPLIES 3
Greg_Deckler
Super User
Super User

@gnhaines Tought to say, not sure I have enough context about the data and what you are doing. But I would love it if measures could return tables. Would really help cut down on repeating the same code in multiple places. 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Greg_Deckler
Super User
Super User

@gnhaines Probably not, measures can't return tables and DATESMTD wants a table of dates.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

@Greg_Deckler 

Ok to a switched to filters in series for the date table and new measures AsAtYear, AsAtMonth, AsAtQuarter so they only need to be calculated once with data refresh.  I've got to wonder if there is a better way to do it that jumps out at you, considering I'm leaping sideways from my expectation that I could create a table from a measure, and still casting off the assumptions I had.

 

Thanks for your reply as well.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!