Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

SUM Values Based On Second Biggest Value From Another Column

Hi everyone,   I have a table that contains many rows. One of the columns is period (YYYYMMDD). Another column is the Sales Amount.   What I want to do is to have a column (measure?) that will sh...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

you are genius Mariusz ðŸ™‚ 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Actually I came across a similar challenge.

     

    The metric you suggested: [Unbilled-Previous-Month = CALCULATE(SUM('All BPOC Reports'[Unbilled]), PARALLELPERIOD('All BPOC Reports'[SourceDate],-1,MONTH)] is working great if I have a table and one of my columns is Dates.

     

    However, what I also would like to have is a stand-alone multi-row card that will always show me two things:

    - Current Month Unbilled [Highest Month, Max]

    - Previous Month Unbilled*

     

    *Previous Month Unbiled (formula above) is showing me the total of all Unbilled values from my table. However, if I select one of the specific Dates, then it will show correctly.

     

    My desired output is:

    a) if nothing is selected, show me the Current Month Unbilled (Highest | Max Month) & Previous Month Unbilled

    b) if I select 1/31/2020, show me the Current Month Unbilled (75M) & Previous Month Unbilled (60M)

     

     

    Point b) is working well, I just need to fix the point a) somehow. I was thinking about embedding MAX formula or creating a var with M-1, but I got a bit lost...

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Ok, below will solve the issue. Not sure if that's the easiest way, but it works.

       

      Unbilled-Previous-Month-02 =
      VAR Period01 = [Measure-00]
      RETURN
      if([Measure-00]<>0,calculate(sum('All BPOC Reports'[Unbilled]),'All BPOC Reports'[SourceDate]=Period01),[Unbilled-Previous-Month])
       
      Measure-00 =
      VAR Date1 = [Max-Period-01]
      VAR Date2 = CALCULATE(MAX('All BPOC Reports'[SourceDate]),FILTER('All BPOC Reports','All BPOC Reports'[SourceDate]<Date1))
      RETURN Date2