Forum Discussion

cristianml's avatar
cristianml
Post Prodigy
4 years ago
Solved

Calculate Fiscal Quarter all except a column string/value variable

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.

 

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • cristianml's avatar
    cristianml
    4 years ago

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

     


     

5 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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's avatar
      cristianml
      Post Prodigy
      4 years ago

      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's avatar
        Ashish_Mathur
        Super User
        4 years ago

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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's avatar
    cristianml
    Post Prodigy
    4 years ago

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