cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Last Year dynamically calculated

Hi there, I'm facing with the following problem.

I need to create an Last Year measure.

then I created a calculated group like this:

v_dim_time and v_fact_sales are related to 1:n

var DayToCompare = today()
var DateLastYear = EDATE(DayToCompare;-12)
return
calculate(
SELECTEDMEASURE ();
year(v_DIM_TIME[CALENDAR_DATE_SQL]) = Year(DateLastYear);
v_DIM_TIME[CALENDAR_DATE_SQL] <= dateLastYear
)

My problem is how can I to made the formula dynamical, saying for example that ytd will be evaluated at today - 365 if no other selection are made by time slicer otherview keeping the value selected ?

1 ACCEPTED SOLUTION
Super User

@Anonymous

Using ISFILTERED()  function you can identify if the column is filtered or not. Based on that, you can switch your logic.

Appreciate with a kudos
🙂

Regards,
Nandu Krishna

2 REPLIES 2
Community Champion

Hi @Anonymous ,

Can you please share the sample data and expected output.

Have calculated values where the column changes dynamically i.e shows Total Cost incase Filter in not selected and shows Total Sales if the filter is selected.

The Date Slicer is from the Calendar Table.

Filter =
var __mini = CALCULATE ( MIN ( Dates[Date] ), ALLSELECTED ( Dates[Date] ) )
var __maxi = CALCULATE ( MAX ( Dates[Date] ), ALLSELECTED ( Dates[Date] ) )
var __mind = MIN(Dates[Date])
var __maxd = MAX(Dates[Date])
var __datediffmin = DATEDIFF(__mind,__mini,DAY)
var __datediffmax = DATEDIFF(__maxd,__maxi,DAY)
return
IF ((__datediffmin = 0 || __datediffmax = 0),[Total Sales],[Total Costs])

You can replace the Total Sales and Total Cost with the YTD and YTD from today.

Let me know if this is what you are looking for.

Regards,
Harsh Nathani

Super User

@Anonymous

Using ISFILTERED()  function you can identify if the column is filtered or not. Based on that, you can switch your logic.

Appreciate with a kudos
🙂

Regards,
Nandu Krishna

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors