Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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:
=(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?
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?
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
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
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
)
)
Best Regards
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
15 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
11 |