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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MatE
Frequent Visitor

How to display remaining number by calculating number field against a date field?

I want to display a number of active users for a given month and year in a line chart. My data file got Year, User ID and exit date. I want to display number of active users for each department in a graph. Users who are still active, wont have any value under Exit date field.  Please note that same user can appear in multiple years

Sample data file

Year, User ID, Department, Exit Date 

2018, 1010, ABC

2019, 1010, ABC, 31/01/2019 12:00:00 AM

2018, 2314, ABC,

2018, 5455, ABC, 23/12/2018 12:00:00 AM

2018, 7676, XYZ

2019, 7676, XYZ, 6/03/2019 10:10:00 PM

2019, 6556, XYZ,

2019, 6767, ABC

 

Thanks. 

 

 

1 ACCEPTED SOLUTION

HI @MatE ,

You can add department filter to dax formula:

Measure =
VAR currDate =
    MAX ( Calendar[date] )
RETURN
    CALCULATE (
        COUNTROWS ( Table ),
        FILTER (
            ALLSELECTED ( Table ),
            [Year] <= YEAR ( currDate )
                && OR ( [Exit Date] <= currDate, [Exit Date] = BLANK () )
        ),
        VALUES ( Table[Department] )
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

Hi @MatE ,

I'd like to suggest you to create a unrelated calendar table, then write a measure to use current calendar date to filter on sample table records.

Measure =
VAR currDate =
    MAX ( Calendar[date] )
RETURN
    CALCULATE (
        COUNTROWS ( Table ),
        FILTER (
            ALLSELECTED ( Table ),
            [Year] <= YEAR ( currDate )
                && OR ( [Exit Date] <= currDate, [Exit Date] = BLANK () )
        )
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks for the reply. How can I group that value by the department? 

HI @MatE ,

You can add department filter to dax formula:

Measure =
VAR currDate =
    MAX ( Calendar[date] )
RETURN
    CALCULATE (
        COUNTROWS ( Table ),
        FILTER (
            ALLSELECTED ( Table ),
            [Year] <= YEAR ( currDate )
                && OR ( [Exit Date] <= currDate, [Exit Date] = BLANK () )
        ),
        VALUES ( Table[Department] )
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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