cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Saap
Resolver II
Resolver II

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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors