Datesinperiod not working as intended

[rjiang]

Hi, I am having issues I suspect is related to the Datesinperiod function:

The visual on the left is showing number of clicks by month by Contact ID

The visual on the right shows the number of clicks in the last 3 months (not including current month) and is calculated as follows:

Clicks L3 = 
    CALCULATE(
        GS_WEBMETRICS_CLICK_MONTH[Number of Clicks],
        DATESINPERIOD(
            GS_WEBMETRICS_CLICK_MONTH[CLICK_MONTH],
            MAX(GS_WEBMETRICS_CLICK_MONTH[CLICK_MONTH])-1,
            -3,
            MONTH
        )
    )

While the totals seem to add up correctly to 1.365m, you'll notice that the visual on the right is not displaying all contacts

For instance, contacts 1, 29, and 35 are in the visual on the left, but absent from the one on the right.

Any suggestions on how to modify the date logic such that the right visual shows all contacts where [Clicks L3] >0 show up?

Thanks

[amitchandak]

@rjiang , Please prefer to use date table marked as date tbale in such cases

Clicks L3 =
CALCULATE(
GS_WEBMETRICS_CLICK_MONTH[Number of Clicks],
DATESINPERIOD(
Date[Date],
MAX(Date[Date])-1,
-3,
MONTH
)
)

Rolling Months Formula: https://youtu.be/GS5O4G81fww

[rjiang]

I forgot to mention the data is coming in from direct query, so I can't create a functioning date table to filter form