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