cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bajimmy1983 Member
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
    )
)

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

 

Thanks in advance and cheers Smiley Happy

 

 

 

 

Jaderson Almeida
Business Coordinator
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

Hi,

 

You may download my solution PBI file from here.

 

Hope this helps.

6 REPLIES 6
Super User
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.

bajimmy1983 Member
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

Jaderson Almeida
Business Coordinator
Highlighted
bajimmy1983 Member
Member

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

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! Smiley Happy

Jimmy

Jaderson Almeida
Business Coordinator
Super User
Super User

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

Hi,

 

You may download my solution PBI file from here.

 

Hope this helps.

bajimmy1983 Member
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! 

Jaderson Almeida
Business Coordinator
Super User
Super User

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

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

 

Thank you.