cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nicole1995
Frequent Visitor

Count and filter between two dates

Hello

 

I have two tables, one with current subscriptions and ones with expired subscriptions linked by memberID. I need to check from the current subscriptions who have renewed and is not a new member.  To do this I want to check for any subscriptions in the Expired table where the end date is between the Current subscription start date - 1 Day and -1 Month. I then need to use the results as part of a pivot table where the row will be subscription type and the column will be start date. 

 

This is an example of my required output

 

nicole1995_0-1599566383506.png


Below is my dax query but I keep getting "a table of multiple values was supplied where a single value was expected"

Renewed =
CALCULATE(COUNTROWS('CurrentSubscriptions'),
FILTER('ExpiredSubscriptions',
'ExpiredSubscriptions'[EndDate] <= DATEADD('CurrentSubscriptions'[StartDate].[Date],-1,DAY)
&& 'ExpiredSubscriptions'[EndDate] >= DATEADD('CurrentSubscriptions'[StartDate].[Date],-1,MONTH)
))
 
Im not sure if what I'm doing is close the right way of handling this. Could anyone please advise?
 
Thanks
1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @nicole1995 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Current Subscription:

c1.png

 

Expired Subscription:

c2.png

 

You may create a measure as below.

Result = 
var t = 
ADDCOLUMNS(
    ALL('Current Subscriptions'),
    "Result",
    var _startdate = [StartDate]
    var _date = EOMONTH(_startdate,-1)
    var _d = DATE(YEAR(_date),MONTH(_date),DAY(_startdate))
    return
    COUNTROWS(
        FILTER(
            ALL('Expired Subscriptions'),
            [MemberID]=EARLIER('Current Subscriptions'[MemberID])&&
            [EndDate]>=_d&&
            [EndDate]<=_startdate-1
        )
    )
)
var _result =
SUMX(
    SUMMARIZE(
        'Current Subscriptions',
        'Current Subscriptions'[Type],
        'Current Subscriptions'[StartDate].[Month],
        "Re",
        SUMX(
            FILTER(
                t,
                [Type]=SELECTEDVALUE('Current Subscriptions'[Type])&&
                'Current Subscriptions'[StartDate].[Month]=EARLIER('Current Subscriptions'[StartDate].[Month])
            ),
            [Result]
        )
    ),
    [Re]
)
return
IF(
    ISBLANK(_result),
    0,
    _result
)

 

Result:

c3.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

View solution in original post

6 REPLIES 6
v-alq-msft
Community Support
Community Support

Hi, @nicole1995 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Current Subscription:

c1.png

 

Expired Subscription:

c2.png

 

You may create a measure as below.

Result = 
var t = 
ADDCOLUMNS(
    ALL('Current Subscriptions'),
    "Result",
    var _startdate = [StartDate]
    var _date = EOMONTH(_startdate,-1)
    var _d = DATE(YEAR(_date),MONTH(_date),DAY(_startdate))
    return
    COUNTROWS(
        FILTER(
            ALL('Expired Subscriptions'),
            [MemberID]=EARLIER('Current Subscriptions'[MemberID])&&
            [EndDate]>=_d&&
            [EndDate]<=_startdate-1
        )
    )
)
var _result =
SUMX(
    SUMMARIZE(
        'Current Subscriptions',
        'Current Subscriptions'[Type],
        'Current Subscriptions'[StartDate].[Month],
        "Re",
        SUMX(
            FILTER(
                t,
                [Type]=SELECTEDVALUE('Current Subscriptions'[Type])&&
                'Current Subscriptions'[StartDate].[Month]=EARLIER('Current Subscriptions'[StartDate].[Month])
            ),
            [Result]
        )
    ),
    [Re]
)
return
IF(
    ISBLANK(_result),
    0,
    _result
)

 

Result:

c3.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

View solution in original post

Greg_Deckler
Super User
Super User

@nicole1995 - DATEADD will return a column of values. Try:

CALCULATE(
COUNTROWS('CurrentSubscriptions'),
FILTER(
'ExpiredSubscriptions',
'ExpiredSubscriptions'[EndDate] <= ('CurrentSubscriptions'[StartDate].[Date] -1) * 1.
&& 'ExpiredSubscriptions'[EndDate] >= EOMONT('CurrentSubscriptions'[StartDate].[Date],-1)
))


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Hi @Greg_Deckler 

 

Thanks for your quick reply.

 

I have tried the query but it won't let me filter between two tables. Is there something I'm missing ?

@nicole1995 - Oh, you probably need an aggregator around a column reference (MAX, MIN, etc.) or MAXX(RELATEDTABLE(...)...)


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

amitchandak
Super User
Super User

@nicole1995 , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

A approach on this blog should help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

Two date join to same date table. Here, in one date we can have -1

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Hi @amitchandak 

 

I have created some sample data 

 

nicole1995_0-1599570434209.png

Hope this makes sense

 

Thanks

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.