DAX Measure for Active Subscribers not plotting in Excel

[MEverts]

Dear, 

I'm looking to plot the number of Open and Answered data-entry queries in Excel using a dax patern for active subscribers/tickets.  

My Source data looks like this, together with a disconected Calendar-table:

ID	Site	#Subject ID	Type	Current Status	Start_date_status	End_date_status
1171	102	102001	System Query	Open State	11/23/2017 14:35	11/23/2017 14:37
1172	102	102001	System Query	Open State	11/23/2017 14:40	11/27/2017 19:49
1173	102	102001	Site Query from PM	Open State	1/4/2018 22:35	1/10/2018 15:05
1173	102	102001	Site Query from PM	Answered State	1/10/2018 15:05	4/16/2018 20:39
1174	102	102001	Site Query from PM	Open State	1/4/2018 22:37	1/10/2018 15:02
1174	102	102001	Site Query from PM	Answered State	1/10/2018 15:02	4/16/2018 20:40
1175	102	102001	Site Query from PM	Open State	1/4/2018 22:38	10/17/2018 16:30
1175	102	102001	Site Query from PM	Answered State	10/17/2018 16:30	10/18/2018 15:16
1176	102	102001	Site Query from PM	Open State	1/4/2018 22:39	1/10/2018 16:17
1176	102	102001	Site Query from PM	Answered State	1/10/2018 16:17	7/20/2018 16:01

I have got it working in Power BI by amending the helpful patern at the Radacad website, which is great:

Active Subscribers =
VAR _CurrDate =
    SELECTEDVALUE( 'Calendar'[Date] )
VAR _FirstStartDate =
    MIN ( 'STATIC_Query Metrics_Final'[Start_date_status] )
VAR _Queries =
    FILTER (
        'STATIC_Query Metrics_Final',
        'STATIC_Query Metrics_Final'[End_date_status] >= _CurrDate
            && 'STATIC_Query Metrics_Final'[Start_date_status] < - _CurrDate
    )
VAR _countRows =
    COUNTROWS ( _Queries )
RETURN
    IF (
        _CurrDate <= TODAY ()
            && _CurrDate >= _FirstStartDate,
        IF ( ISBLANK ( _countRows ), 0, _countRows )
    )

However, I would like to have a similar plot in an Excell dashboard.

As the SELECTEDVALUES command is not available in Excel 365, I instead use IF(HASONEVALUE( 'Calendar'[Date]),VALUES( 'Calendar'[Date]), as seen below for the first Variable _CurrDate:

Active Subscribers :=
VAR _CurrDate =
    IF ( HASONEVALUE ( 'Calendar'[Date] ), VALUES ( 'Calendar'[Date] ) )
VAR _FirstStartDate =
    MIN ( 'STATIC_Query Metrics_Final'[Start_date_status] )
VAR _Queries =
    FILTER (
        'STATIC_Query Metrics_Final',
        'STATIC_Query Metrics_Final'[End_date_status] >= _CurrDate
            && 'STATIC_Query Metrics_Final'[Start_date_status] < - _CurrDate
    )
VAR _countRows =
    COUNTROWS ( _Queries )
RETURN
    IF (
        _CurrDate <= TODAY ()
            && _CurrDate >= _FirstStartDate,
        IF ( ISBLANK ( _countRows ), 0, _countRows )
    )

Perhaps my understanding of the Excel alternative for SELECTEDVALUE incorrect, but unfortunately when create a area plot in the exact same way as in Power BI, there is no reasonable output, just a blank graph with an y-axis from 0 to 1:

I must be doing something wrong; any help or insight you could give me is much appreciated.

Best regards,

Martijn

[Mariusz · Accepted solution]

Hi @MEverts 

You can use 

VAR _CurrDate =
    MAX( 'Calendar'[Date] )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[MEverts]

Dear Mariusz,

Thank you so much!! It works:-)

Best regards,

Martijn

[Mariusz]

Hi @MEverts 

No problem, happy to help!

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn