Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
rjiang
Helper I
Helper I

Datesinperiod not working as intended

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

 

 

rjiang_1-1649351247242.png

 

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

2 REPLIES 2
amitchandak
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.