How to Sum up DAX with "DATESINBETWEEN"

[Anonymous]

Hi Power BI, 

I created a New Measure being "

Total Ans. Chats (Prev Mth) = CALCULATE(SUM('2 Chatsdata'[Chats/Visitors]),'2 Chatsdata'[Chats (type)]="chats",DATESBETWEEN('2 Chatsdata'[Date (MMYY)],[Date2 (start)],[Date2 (end)]))

The issue I have is when I slect more than 1 Month, it is not totalling (instead it select the Latest Month value)

Date2 (start) = MAX('2 Chatsdata'[Date (MMYY)]) - 45

Date2 (end) = MAX('2 Chatsdata'[Date (MMYY)]) - 15

How do i get this to total ?

[v-yulgu-msft]

Hi @Anonymous,

Per my understanding, the result of a measure will be affected by context, that is why you cannot get the total value aftering using DatesBetween to filter.

In this scenario, we can use column to get the desired result.

In your source table '2 Chatsdata', create below calculated columns:

Date2 (start) col =
DATEADD ( '2 Chatsdata'[Date (MMYY)], -1, MONTH )

Sum current month =
CALCULATE (
    SUM ( '2 Chatsdata'[Chats/Visitors] ),
    ALLEXCEPT (
        '2 Chatsdata',
        '2 Chatsdata'[Chats (type)],
        '2 Chatsdata'[Date (MMYY)]
    )
)

Rank =
IF (
    [Date2 (start) col] <> BLANK (),
    RANKX (
        FILTER (
            '2 Chatsdata',
            '2 Chatsdata'[Chats (type)] = EARLIER ( '2 Chatsdata'[Chats (type)] )
        ),
        '2 Chatsdata'[Date (MMYY)],
        ,
        ASC,
        DENSE
    ),
    BLANK ()
)

Sum previous month =
IF (
    '2 Chatsdata'[Chats (type)] = "chats",
    LOOKUPVALUE (
        '2 Chatsdata'[Sum current month],
        '2 Chatsdata'[Chats (type)], '2 Chatsdata'[Chats (type)],
        '2 Chatsdata'[Rank], '2 Chatsdata'[Rank] - 1
    ),
    BLANK ()
)

Best regards,
Yuliana Gu

[Anonymous]

thank you for attempting this.. I can see it working on your PBI.. For me the DAX is well beyond my ability to comprehend... I also cannot get it to work on my PBI.. Lets close this.