cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Resolver I

YTD Calculation

Hi All,

Please help me in correcting my YTD calculation as its giving me blank value since starting of the new year.

Previous Calculation Used ( It was working corretly till 31st Dec):

YTD ACV =
IF (
MONTH ( TODAY () ) = 1,
CALCULATE (
[ACV],
FILTER (
ACV,
YEAR ( ACV[CLOSE_DATE] )
= YEAR ( TODAY () )
&& MONTH ( ACV[CLOSE_DATE] )
<= MONTH ( TODAY () )-1
)
),
CALCULATE (
[ACV],
FILTER (
ACV,
YEAR ( ACV[CLOSE_DATE] ) = YEAR ( TODAY () )
&& MONTH ( ACV[CLOSE_DATE] )
<= MONTH ( TODAY () ) -1
)
)
)
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Try this:

`CALCULATE(    SUM(FactsTable[Data]),    DATESINPERIOD('Date'[DateKey], TODAY(), -12, MONTH))`
4 REPLIES 4
Anonymous
Not applicable

Hi,

could you please state the desired result?

Are you looking for data in the current year? Data in the last 12 months from the current date?

Resolver I

Yes I want data in the last 12 months from current month. So my YTD values will show data till Dec'22.

My calculation was working completely fine till last week, not sure where the problem is in the calculation that its showing blank now.

Anonymous
Not applicable

Try this:

`CALCULATE(    SUM(FactsTable[Data]),    DATESINPERIOD('Date'[DateKey], TODAY(), -12, MONTH))`
Resolver I

Thanks for the help.

It worked after few corrections !!!

YTD ACV =
IF (
MONTH ( TODAY () ) = 1,
CALCULATE(
[ACV],
DATESINPERIOD(ACV[CLOSE_DATE], TODAY(), -12, MONTH)
),
CALCULATE (
[ACV],
FILTER (
ACV,
YEAR ( ACV[CLOSE_DATE] ) = YEAR ( TODAY () )
&& MONTH ( ACV[CLOSE_DATE] )
<= MONTH ( TODAY () ) -1
)
)
)

Announcements

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors