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
Bfaws
Helper III
Helper III

Creating a measure that looks at two date columns

Hello, 

 

I hope someone can help.

 

I have a list of employees who have allowances.  It essenstially shows the start date and the end date.  If the end date if open ended it shows as 31/12/2099.  What i would liek to know is how many employees had an allowances at a specific point in time. Looking at the raw data below:

 

Allowance date range.JPG

 

I expect to see the following:

 

Year                    Number employees who had allowance

2021                   2    (kate bishop & kyle sinclare as both allowances were active in 2021)

2020                   2    (Kate Bishop & John Smith as both allowances were active in 2020)

2019                   1    (only john smith has an active allowance in 2019)

 

Please see link below for .pbix file with raw data and calendar table.

 

Any help would be much appeciated.

 

Thanks, 

 

Brendan

Click here for file 

1 ACCEPTED SOLUTION

@Bfaws,

 

The latest date in your calendar table is 19-May-2020. The visual uses Year from the date hierarchy of Date. It's best practice to mark your calendar table as a date table (see link below). This will remove the automatic date hierarchy for each date field. You can create a calculated column for Year using the YEAR function.

 

DataInsights_0-1634303906887.png

 

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/ 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Bfaws
Helper III
Helper III

@DataInsights 

 

Many thanks - this is very helpful and has solved my issue.

 

Thanks, 

 

Brendan

DataInsights
Super User
Super User

@Bfaws,

 

Try these measures. I created the table SlicerYear since the example Calendar table doesn't contain 2021.

 

Employees with Allowance Calc = 
VAR vYear =
    MAX ( SlicerYear[Year] )
VAR vResult =
    SUMX (
        Allowances,
        VAR vYearFrom =
            YEAR ( Allowances[Date from] )
        VAR vYearTo =
            YEAR ( Allowances[Date to] )
        RETURN
            IF ( vYear >= vYearFrom && vYear <= vYearTo, 1 )
    )
RETURN
    vResult

Employees with Allowance = 
// Calculates totals
SUMX ( VALUES ( SlicerYear[Year] ), [Employees with Allowance Calc])

 

Use SlicerYear[Year] in a visual:

 

DataInsights_0-1634220900897.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@DataInsights 

 

Many thanks for this - its looks like it could solve my issue (and many others).  Unfortunately I cant replicate it exactly.  I think the issue is I can`t create your Year Slicer.  I have updated my calendar table to include 2021, however, when doing this the data for 2021 is not showing - although 2021 is now present in the table.

 

Link to .pbix file. 

 

I`d really appreciate it if you could have a look for me.

 

Many thanks.

@Bfaws,

 

The latest date in your calendar table is 19-May-2020. The visual uses Year from the date hierarchy of Date. It's best practice to mark your calendar table as a date table (see link below). This will remove the automatic date hierarchy for each date field. You can create a calculated column for Year using the YEAR function.

 

DataInsights_0-1634303906887.png

 

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/ 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.