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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jts44246
New Member

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

I have an Excel formula that calculates the percent of fiscal year that elapsed for the current date, 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:

Copy
 
=(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:

Copy
 
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?

3 REPLIES 3
jts44246
New Member

Thanks,

 

Where are you entering this script? I am adding it to the Edit Calculated Measure window (below) but I receive the follow error (further below).  Should this go somewhere else?

jts44246_1-1709223717099.png

 

jts44246_0-1709223699359.png

 

Hi @jts44246 ,

What I using is Power BI Desktop, and create a measure in it. You can find the details in the attachment.

Get started with Power BI Desktop - Power BI | Microsoft Learn

vyiruanmsft_0-1709277401504.png

vyiruanmsft_1-1709277948966.png

 

Where do you want to apply the calculation logic?  Excel or other software? Could you please provide some sample data and the expected result with backend scenario?

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yiruan-msft
Community Support
Community Support

Hi @jts44246 ,

You can create a measure as below with the same formula in Excel to get the same result...

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

vyiruanmsft_0-1709193493655.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors