CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Serdia
Serdia

Reputation: 4428

How to find the last value ordered by a different column

I have a table MyTable has columns: QuoteID, ControlNo, Premium, ExpirationDate

I need to create a measure that would grab the SUM(Premium) and EffectiveDate should be <= Today() and last ExpirationDate (ordered by QuoteID DESC) should be >= Today().

How can I translate below statement to DAX?

In SQL, I would do this way:

select sum(Premium) as Premium
from MyTable t
where EffectiveDate <= GETDATE() and
      (select top 1 t2.ExpirationDate
       from MyTable t2
       where t2.ControlNo = t.controlno
       order by t.quoteid desc) >= GETDATE()

How can I write it in DAX?

I've tried this, but it's not working properly:

Premium =
CALCULATE (
    SUM ( fact_Premium[Premium] ),
    FILTER (
        fact_Premium,
        fact_Premium[EffectiveDate] <= TODAY () &&
        TOPN ( 1, ALL ( fact_Premium[ExpirationDate] ), 
                  fact_Premium[QuoteID], ASC ) >= TODAY ()
    )
)

UPDATE:

Trying to create calculated table from fact_Premium dataset, but still not sure how can I filter it

In_Force Premium = 
    FILTER(
        ADDCOLUMNS(
        SUMMARIZE(
           //Grouping necessary columns
            fact_Premium,
            fact_Premium[QuoteID],
            fact_Premium[Division],
            fact_Premium[Office],
            dim_Company[CompanyGUID],
            fact_Premium[LineGUID],
            fact_Premium[ProducerGUID],
            fact_Premium[StateID],
            fact_Premium[ExpirationDate]
                  ),    
            "Premium", CALCULATE(
                                SUM(fact_Premium[Premium])
                                ),
            "ControlNo", CALCULATE(
                            DISTINCTCOUNT(fact_Premium[ControlNo])
                                  )

   ), // Here I need to make sure TODAY() falls between fact_Premium[EffectiveDate] and (SELECT TOP 1 fact_Premium[ExpirationDate] ORDE BY QuoteID DESC)
    )

Upvotes: 1

Views: 354

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40244

There's a couple of problems with the DAX here.

First, when you use TOPN, your ordering expression (3rd argument) can only reference rows in the table you are operating on (2nd argument), so only using the [ExpirationDate] column there won't work. I think you probably want fact_Premium instead of ALL ( fact_Premium[ExpirationDate] ).

Second, the TOPN function returns a table rather than a single value, so you need to access the column you want somehow. One option would be to use an iterator like SUMX or MAXX:

MAXX( TOPN(...), fact_Premium[ExpirationDate] )

You could also use SELECTCOLUMNS which will coerce a single row, single column table to just be a value:

SELECTCOLUMNS( TOPN(...), "ExpirationDate", fact_Premium[ExpirationDate] )

I can't guarantee that this will work perfectly, but it should get you closer to your goal:

Premium =
CALCULATE (
    SUM ( fact_Premium[Premium] ),
    FILTER (
        fact_Premium,
        fact_Premium[EffectiveDate] <= TODAY () &&
        SUMX( TOPN ( 1, fact_Premium, fact_Premium[QuoteID], DESC ),
              fact_Premium[ExpirationDate] )
            >= TODAY ()
    )
)

Upvotes: 1

Related Questions