cancel
Showing results for
Did you mean:
Member

## SUM With FIXED Time Period (From Year A to Year B) at Once

Hi all!

Anyone had to perform similar Measure calculation such this one? Could you help? I've tried so many ways, but all failed because specific for this case I don't know how to IGNORE FILTERED Year and then the measure consider both years (A & B) in the calculation at one shot. Le me explain better...

Objetive:

Calculate Realized amount between Year Before Selected . 04. 01 and Year Selected. 03. 01

Example considering 2018 has been selected in a Slicer: Period from 2017.04.01 to 2018.03.01

I want a measure that turns the YEAR part dynamic and this is the challenge here, because if I use "...

DATESBETWEEN(

'Date'[Date]; DATE( SELECTEDVALUE( Date[Year] ) -1; 4; 1 ); DATE( SELECTEDVALUE( Date[Year] ); 3; 1 )

)

OR

DATESINPERIOD(
'Date'[Date]; SELECTEDVALUE( Date[Year] ) -1; 4; 1 ); +12; MONTH )

Both method as FILTERs in a CALCULATE function failed because the fact that a Year is selected in the Slicer. So the calculation remains stucked in that year, only, but I need it pass through Year A to Year B

First Attempt

```Realized FIXED_PERIOD =
CALCULATE (
[_Total BD_Pilot Amount (Currency REQUIRED) LAST BASE];
FILTER ( BD_Pilot; BD_Pilot[Vendor] = _GLOBO );
DATESBETWEEN (
'Date'[Date];
DATE ( SELECTEDVALUE ( Date[Year] ) - 1; 4; 1 );
DATE ( SELECTEDVALUE ( Date[Year] ); 3; 1 )
)
)```

Second Attempt:

```Realized FIXED_PERIOD =
CALCULATE (
[_Total BD_Pilot Amount (Currency REQUIRED) LAST BASE];
FILTER ( BD_Pilot; BD_Pilot[Vendor] = _GLOBO );
DATESINPERIOD (
'Date'[Date];
DATE ( SELECTEDVALUE ( Date[Year] ) - 1; 4; 1 );
+12;
MONTH
)
)```

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

Hi,

Hope this helps.

6 REPLIES 6
Super User

## Re: SUM With FIXED Time Period (From Year A to Year B) at Once

Hi,

It looks like you want to compute a YTD figure where your financial year is April to March.  Try this measure

Realized FIXED_PERIOD = CALCULATE ( [_Total BD_Pilot Amount (Currency REQUIRED) LAST BASE]; FILTER ( BD_Pilot; BD_Pilot[Vendor] = _GLOBO ); DATEYTD ( 'Date'[Date]; "31/3"))

Hope this helps.

Member

## Re: SUM With FIXED Time Period (From Year A to Year B) at Once

Hi @Ashish_Mathur, first thank you for your fast reply... I'm going to try it know. Before your post, I was preparing a .pbix file to attach here as sample data. Give me a few minutes and I get back here!

Best,

Jimmy

Member

## Re: SUM With FIXED Time Period (From Year A to Year B) at Once

It didn't work. Please see the files as Sample Data (OneDrive). I have reproduced the others Measures as attempts. Just one works as I want, but this one is hardcoded and this is not good because User won't be able to interact with years according to slicer.

Thanks again for your time and cheers!

Jimmy

Highlighted
Super User

Hi,

Hope this helps.

Member

## Re: SUM With FIXED Time Period (From Year A to Year B) at Once

Hi again @Ashish_Mathur

WOW! This solution is fantastic. It is perfect for this scenario. Another great point is I can also adapt your solution to increment some additional rules. You really opened my mind now!

Thank you so much for your time! BTW, do you have some sort of PBI channel on Youtube or anywhere else?

Cheers!