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
DataStraine
Advocate I
Advocate I

Ignore Future Dates in Calculations?

I have an issue due to the way my data is currently being calculated that I hope you can help me with. Previously, I got help on creating a forumla to properly get churned customers. You can find the help I recieved  here. The issue I have is that the way the current data works is that I have future dates in my calendar table because subscriptions can last for up to 5 years. When I use the formula as written in that response with my current data, I get all future dates as well. I need to hardcode the end date filter in my churn forumla or in the sumx forumla in order to stop showing future dates in visuals and other displayed resutls. I know I can limit this in visuals through filters; however, due to the way that visuals interact, it carries the carries the filter forward which I do not want. Is there a way to apply a filter onto either of these formulas in order to ignore all dates beyond the current (real time) month? 

Note - I cannot edit the calendar table as the way it is set up currently is needed for other forumlas and functions. 

Current forumulas

 

 

Churned Count = SUMX(
                    VALUES('Calendar'[Month-Year]),
                    [Churned Count Helper]
                )

Churned Count Helper = 
        COUNTROWS(
                FILTER(
                     CALCULATETABLE(
                         VALUES(Customers[Tenant Id])),
                    CALCULATE([Total Billable Count],
                        DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-6),
                        MAX('Calendar'[Date])
                        )) = 0 && 
                    CALCULATE([Total Billable Count],
                        DATESBETWEEN('Calendar'[Date],
                                    EDATE(MIN('Calendar'[Date]),-7),
                                    EOMONTH(EDATE(MIN('Calendar'[Date]),-7),0)
                                    )
                                )>0
                        )
                    )

 

 

Attempted Forumla

 

 

Churned Count Helper = 
        COUNTROWS(
                FILTER(
                     CALCULATETABLE(
                         VALUES(Customers[Tenant Id])),
                    CALCULATE([Total Billable Count],
                        DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-6),
                        MAX('Calendar'[Date])
                        )) = 0 && 
                    CALCULATE([Total Billable Count],
                        DATESBETWEEN('Calendar'[Date],
                                    EDATE(MIN('Calendar'[Date]),-7),
                                    EOMONTH(EDATE(MIN('Calendar'[Date]),-7),0)
                                    )
                                )>0 && CALCULATE([Total Billable Count], 'Calendar'[Date] <= EOMONTH(TODAY(), 0))
                        )
                    )

 

 

 

 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

Hello @DataStraine 

Take a look at this article.  It covers the topic in detail and gives a solution.
https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/

 

View solution in original post

2 REPLIES 2
jdbuchanan71
Super User
Super User

Hello @DataStraine 

Take a look at this article.  It covers the topic in detail and gives a solution.
https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/

 

Thank you! That indeed worked. I'm still learning how to do filters in Power BI and I was trying to do the same thing without having to make a new column when I should have just did it that way like in Pandas ha!

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.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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