Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@Anonymous 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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors