CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Serdia
Serdia

Reputation: 4428

How to summarize data into table filtering by dates

I need to summarize dax table and filter it by date range 5 month back from last EffectiveDate, which is 7/27/2019

So my dax expression:

TestTable1 = 
VAR LastEffDate = LASTDATE(fact_Premium[EffectiveDate])  // --  7/27/2019
RETURN 
SUMMARIZE(
      FILTER(dim_Date, DATEDIFF(DATEADD(STARTOFMONTH(LastEffDate), -5,MONTH), ENDOFMONTH(LastEffDate), MONTH)),
      dim_Date[Year Month],
       "Premium", [Ttl WP]
       )

But for some reason it brings me data for all years in a dataset:

enter image description here

I also tried:

TestTable1 = 
    VAR LastEffDate = LASTDATE(fact_Premium[EffectiveDate])  // --  7/27/2019
RETURN 
    SUMMARIZE (
        FILTER (
             dim_Date, 
             DATESBETWEEN(dim_Date[Date],
                 DATE(2019,5,1),               
                 DATE(2019,6,1)
             )
        ),
        dim_Date[Year Month],
        "Premium", [Ttl WP]
    )

But it gives me an error: 

A table of multiple values was supplied where a single value was expected.

Am I missing something here?

Upvotes: 0

Views: 4024

Answers (2)

Mboolean
Mboolean

Reputation: 392

I think you are still missing aggregating your Premium,

SUM(Ttl WP)

TestTable1 =
VAR LastEffDate =
    LASTDATE ( fact_Premium[EffectiveDate] ) // --  7/27/2019
RETURN
    SUMMARIZE (
        FILTER (
            dim_Date,
            DATESBETWEEN ( dim_Date[Date], DATE ( 2019, 5, 1 ), DATE ( 2019, 6, 1 ) )
        ),
        dim_Date[Year Month],
        "Premium", SUM ( [Ttl WP] ) ---- Need to aggregate here
    )

Upvotes: 0

Pratik Bhavsar
Pratik Bhavsar

Reputation: 848

Your filter expression should be using DATESBETWEEN and not the datediff: 

TestTable1 = 
    VAR LastEffDate = LASTDATE(fact_Premium[EffectiveDate])  // --  7/27/2019
RETURN 
    SUMMARIZE (
        FILTER (
             dim_Date, 
             DATESBETWEEN(
                 //expression for start date, 
                 //expression for end date
             )
        ),
        dim_Date[Year Month],
        "Premium", [Ttl WP]
    )

Upvotes: 2

Related Questions