topic Datesinperiod not working as intended in DAX Commands and Tips

Datesinperiod not working as intended

rjiang — Thu, 07 Apr 2022 17:15:45 GMT

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

Re: Datesinperiod not working as intended

amitchandak — Fri, 08 Apr 2022 01:55:39 GMT

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

Re: Datesinperiod not working as intended

rjiang — Fri, 08 Apr 2022 04:00:10 GMT

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