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

[bajimmy1983]

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
    )
)

If you have any ideas, tips and tricks, please help me! 

Thanks in advance and cheers 🙂

[Ashish_Mathur]

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.

[bajimmy1983]

Hi @Ashish_Mathur, 

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution PBI file from here.

Hope this helps.

[paulacaza]

Helped me a lot too! Thank you!

[bajimmy1983]

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! 

[Ashish_Mathur]

You are welcome.  You may please visit my Blog here - http://www.ashishmathur.com/knowledge-base/.

Thank you.

[bajimmy1983]

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