Adapting Excel formula to Calculated Measure for % of Fiscal Year Elapsed

Question

I have an Excel formula that calculates % of fiscal year elapsed, and I want to use it in a calculated measure to add it as a dimension field on a PivotTable Cube. However, Copilot generated an error when I tried to adapt it to a calculated measure. Here's the Excel formula:

=(1-((DATE(IF(MONTH(TODAY()-1)>7,YEAR(TODAY()-1)+1,YEAR(TODAY()-1)),6,30)-TODAY()-1)/365))

And here's what Copilot generated:

WITH MEMBER [Measures].[MyCalculatedMeasure] AS
    '1 - (
      (
        DateSerial(
          IIf(Month(NOW() - 1) > 7, Year(NOW() - 1) + 1, Year(NOW() - 1)),
          6,
          30
        ) - NOW() - 1
      ) / 365'
SELECT
  [Measures].[MyCalculatedMeasure] ON 0
FROM [munprod_General Ledger Cubes]

Does anyone know how to fix this error and adapt the Excel formula to a calculated measure?

Answer 1

Hi @James Stone In my opinion, the Excel formula can be created as a measure in Power Pivot directly.

Since the Q&A forum is responsible for general Excel usage questions, for measures of Power Pivot, you can go to Microsoft Fabric Community. (For better help, you may add a sample in your post.)

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/bd-p/DAXCommands

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.******************************************** If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---