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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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