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
Saap
Resolver III
Resolver III

Difference to last month using disconnected time table

Hi all,

In my fact table I have 3 columns: [Employee], [ContractStart] and [ContractStop].
I also have a disconnected time table.

Using dates from disconnected time table and below measure I count the number of employees with active contract in every month.

ACTIVE =

CALCULATE( DISTINCTCOUNT( Employees[Employee] ),

    FILTER( VALUES( Employees[ContractStart] ), Employees[ContractStart] <= MAX( Dates[Date] ) ),

    FILTER( VALUES( Employees[ContractStop] ), OR( Employees[ContractStop] >= MIN( Dates[Date] ), ISBLANK( Employees[ContractStop] ) ) ) )
Saap_0-1654847634063.png


I would like to count the difference in the number of active employees to last month:

YYYY-MMACTIVEDESIRED OUTCOME LAST MONTHDESIRED OUTCOME DIFFERENCE
2020-101 1
2020-1124123
2020-12592435
2021-01875928
2021-028587-2
2021-037585-10
2021-0476751
2021-056876-8
2021-06 68-68


I tried using the CALCULATE with PREVIOUSMONTH function but it doesn't work:

ACTIVE LM = CALCULATE( [ACTIVE], PREVIOUSMONTH( Dates[Date] ))

Saap_1-1654847931545.png


Is there a way to count that?

Here is the link for the sample pbix file: https://filetransfer.io/data-package/rEoww2op#link



1 ACCEPTED SOLUTION
johnt75
Super User
Super User

Right click on your Date table and choose "Mark as date table". Time intelligence functions like PREVIOUSMONTH need a proper date table.

Nothing else needs changing, you don't need a relationship from Date to Employees and the measure you have works fine.

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

Right click on your Date table and choose "Mark as date table". Time intelligence functions like PREVIOUSMONTH need a proper date table.

Nothing else needs changing, you don't need a relationship from Date to Employees and the measure you have works fine.

Hi@johnt75 

Thanks, that works perfectly.
I didn't think that marking date table as a date table would solve this problem.
In my other reports I didn't mark the date table and PREVIOUSMONTH worked anyway so I thought the problem lies somewhere else.

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.