Calculate Fiscal Quarter all except a column string/value variable

[cristianml]

Hi,

I need help with a measure. I'm trying to calculate the Starting revenue value for each quarter/period filtered.

I have the Q1 with V2  but then I have to apply the filter using ALLEXCEPT or ALL and then filter again ..

Expecting result:

__ STARTING Q REVENUE =

VAR V1 = SELECTEDVALUE('Calendar'[FY & Q])

VAR V2 = LEFT(V1,6) & "1"

VAR V3 = [_Actual + Backlog Rev]

RETURN

CALCULATE(V3,ALLEXCEPT('Calendar','Calendar'[FY & Q]),'Calendar'[FY & Q]=V2)

I know I'm close with this measure but is not working at the moment. 

Hope you can help me.

[cristianml · Accepted solution]

I found the solution:

__STARTING Q REVENUE =

VAR _Quarter = SELECTEDVALUE ( 'Calendar'[FY & Q] )

VAR _Start = CALCULATE ( MIN ( 'Calendar'[Date] ), ALL ( 'Calendar' ), 'Calendar'[FY & Q] = _Quarter )

RETURN

CALCULATE (

[_Actual Rev],

DATESINPERIOD ( Calendar[Date], _Start, 3, MONTH )

)

)

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  When does your FY start?  Do you have a Date column in your raw dataset?

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  When does your FY start?  Do you have a Date column in your raw dataset?

[cristianml]

Hi @Ashish_Mathur ,

I can´t share the file. And yes I have a date table:  

I'm not sure why you need when date start. I don't want to hardcode the measure. The criteria should be that the start Q is the one that ends with 1.

That´s why I use VAR1 and VAR2 that defines the first Quarter of that point in time/period.

And with that I need to filter but not sure how to use ALLEXCEPT/ALL/FILTER, etc in that last part.

[Ashish_Mathur]

I will need data to work with.  Anonymise the data, if feasible.