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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Gokul_Saraboji
Frequent Visitor

Issue with Calculating PM (Previous Month) Measure

Hello Power BI Community,

I'm encountering an issue with my DAX measure for calculating the PM (Previous Month) . Here's the context:

Problem Overview with scenario:

  • I have a date range between slicer that allows users to select a period, such as from 02-09-2024 to 04-01-2025
  • am using DATESMTD to find CM employees its works correctly,showing values from 01-01-2025 to 04-01-20225.
  • CM Employees = CALCULATE([Total Employees],DATESMTD(Dim_Calendar[Date])).
  • Total Employees = DISTINCTCOUNT(Worksheet[Name])
  • The PM Employees should show the values from  01-12-2024 to 04-12-2024 , i couldn't able to get this 
  • if i use PARALLELPERIOD measure also it giving the previous month entire value(01-12-2024 to 31-12-2024)

    Please provide a solution or any suggestions on how to resolve this issue.
    Thanks in advance!
 
 
 

 

15 REPLIES 15
v-cgao-msft
Community Support
Community Support

Hi @Gokul_Saraboji ,

 

I'm so sorry for the trouble.  I've unmarked the reply as the solution.

Best Regards,
Gao

Community Support Team

Ray_Minds
Responsive Resident
Responsive Resident

Hi @Gokul_Saraboji 

Solution 1: Please Try This:-

 

1. Total Employees: This calculates the distinct count of employees for the selected period.

 

Total Employees = DISTINCTCOUNT(Worksheet[Name])

 

2. CM Employees: This calculates the employees in the current month (January 2025, in this case).

 

CM Employees = 
CALCULATE(
    [Total Employees],
    DATESMTD(Dim_Calendar[Date])
)

 

3. PM Employees: This calculates employees for the aligned period in the previous month (01-Dec-2024 to 04-Dec-2024).(Custom Previous Month)

 

PM Employees = 
CALCULATE(
    [Total Employees],
    DATESINPERIOD(
        Dim_Calendar[Date],
        DATEADD(MIN(Dim_Calendar[Date]), -1, MONTH),
        DATEDIFF(MIN(Dim_Calendar[Date]), MAX(Dim_Calendar[Date]), DAY),
        DAY
    )
)

 

Ray_Minds_0-1736316268945.jpeg

 

 If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-huijiey-msft
Community Support
Community Support

Hi @Gokul_Saraboji ,

 

Thank you for your interest in this case.

 

This is the data I created to test, and I'm going to show you by calculating the ID distinct count for the specified date range for the current month (CM) and the previous month (PM).

Date

ID

2024-09-13

AA

2024-10-12

BB

2024-10-19

CC

2024-11-03

DD

2024-10-26

EE

2024-12-20

FF

2024-11-11

GG

2024-10-17

HH

2024-09-29

II

2024-11-09

JJ

2024-10-15

KK

2024-10-14

LL

2024-11-29

MM

2024-11-16

NNN

2024-10-02

OO

2024-10-06

PP

2024-09-09

QQ

2024-10-09

RR

2024-11-02

SS

2024-09-21

TT

2024-12-24

UU

2024-10-28

VV

2024-11-15

WW

2024-09-14

XX

2024-12-03

YY

2024-11-06

ZZ

2024-10-22

AA

2024-12-06

BB

2024-10-01

CC

2024-12-24

DD

2024-10-09

EE

2024-11-22

FF

2025-01-02

GG

2024-12-20

HH

2024-10-23

II

2024-12-07

JJ

2024-12-08

KK

2024-10-02

LL

2024-10-05

MM

2024-10-07

NNN

2024-10-23

OO

2024-09-13

PP

2024-12-01

QQ

2024-12-18

RR

2024-10-27

SS

2024-12-25

TT

2024-09-11

UU

2024-12-22

VV

2024-11-02

WW

2024-10-04

XX

2024-09-20

YY

2024-12-04

ZZ

2024-09-23

AA

2024-11-06

BB

2025-01-01

CC

2024-10-31

DD

2024-09-13

EE

2024-10-05

FF

2024-10-19

GG

2024-12-04

HH

2024-10-16

II

2024-10-26

JJ

2024-09-26

KK

2024-12-15

LL

2024-09-13

MM

2024-09-29

NNN

2024-11-18

OO

2024-10-24

PP

2024-11-23

QQ

2024-10-18

RR

2024-12-18

SS

2024-09-25

TT

2024-11-26

UU

 

Create a measure to calculate Count:

Count = DISTINCTCOUNT('Table (2)'[ID])

 

Create a measure to calculate CM:

CM = 
VAR _currentmaxday =
    DAY ( MAX ( 'Table'[Date] ) )
RETURN
    CALCULATE (
        'Table (2)'[Count],
        FILTER (
            ALLSELECTED ( 'Table (2)' ),
            'Table (2)'[Date] >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
                && 'Table (2)'[Date]
                    <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), _currentmaxday )
        )
    )

 

Create a measure to calculate PM:

PM = 
VAR _currentmaxday =
    DAY ( MAX ( 'Table'[Date] ) )
RETURN
    CALCULATE (
        'Table (2)'[Count],
        FILTER (
            ALLSELECTED ( 'Table (2)' ),
            'Table (2)'[Date]
                >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
                && 'Table (2)'[Date]
                    <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, _currentmaxday )
        )
    )

 

Select a period of dates in slicer to see the values displayed by the three measures:

vhuijieymsft_0-1736235972319.png

 

The pbix file is attached.

 

If you have any other questions please feel free to contact me.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Thanks @v-huijiey-msft 
somewhat its working correctly for example the date range is 02-09-2024 to 04-01-2025,the CM is showing correct value and PM is also working fine.

But here in the attached file, i will explain an scenario,if the date range is select between 02-09-2024 to 02-12-2024 means
--->the CM measure is turning into BLANk,The CM measure should return 1


--->the PM measure is showing value as 1,which is incorrect ,it should show 2 as per the data!(sameperiod last month)Screenshot 2025-01-07 135435.png


And in the same way if i select the data range between 01-01-2025 to 04-01-2025 means 
---> the CM measure is resulting the correct value but
----> the PM measure is rsulting BLANK,which is in correct it should return 1

 

Gokul_Saraboji_0-1736239220205.png

 

Hi @Gokul_Saraboji ,

 

Based on the previous discussion, please try this measure and let me know the results.

PMTD Employees = CALCULATE( [Total Employees], DATEADD( 'Dim_Calendar'[Date], -1, MONTH ) )

vcgaomsft_0-1737684853810.png

I've attached the .pbix file for your convenience.


Best Regards,
Gao

Community Support Team

Hi @Gokul_Saraboji ,

 

Thanks for the reply from Ray_Minds .

 

Modify the syntax of CM and PM a bit:

 

CM =
VAR _currentmaxday =
    MAX ( 'Table'[Date] )
RETURN
    CALCULATE (
        'Table (2)'[Count],
        FILTER (
            ALLSELECTED ( 'Table (2)' ),
            'Table (2)'[Date]
                >= DATE ( YEAR ( _currentmaxday ), MONTH ( _currentmaxday ), 1 )
                && 'Table (2)'[Date]
                    <= DATE ( YEAR ( _currentmaxday ), MONTH ( _currentmaxday ), DAY ( _currentmaxday ) )
        )
    )
PM =
VAR _currentmaxday =
    MAX ( 'Table'[Date] )
RETURN
    CALCULATE (
        'Table (2)'[Count],
        FILTER (
            ALLSELECTED ( 'Table (2)' ),
            'Table (2)'[Date]
                >= DATE ( YEAR ( _currentmaxday ), MONTH ( _currentmaxday ) - 1, 1 )
                && 'Table (2)'[Date]
                    <= DATE ( YEAR ( _currentmaxday ), MONTH ( _currentmaxday ) - 1, DAY ( _currentmaxday ) )
        )
    )

 

 

When selecting 2024-9-2 through 2024-12-2 in the slicer, the CM has 1 piece of data and the PM has 2 pieces of data.

vhuijieymsft_0-1736321748568.png

vhuijieymsft_1-1736321748571.png

 

When selecting 2025-1-1 through 2025-1-4 in the slicer, the CM has 2 piece of data and the PM has 4 pieces of data.

vhuijieymsft_2-1736321758675.png

vhuijieymsft_3-1736321758677.png

 

The pbix file is attached.

 

If you have any other questions please feel free to contact me.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

danextian
Super User
Super User

Hi @Gokul_Saraboji 

 

You can use DATEADD to shift the dates back to a month prior.

 

MTD Revenue =
CALCULATE ( [Total Revenue], DATESMTD ( Dates[Date] ) )

MTD Revenue PM =
CALCULATE ( [MTD Revenue], DATEADD ( Dates[Date], -1, MONTH ) )

danextian_0-1736160833139.png

 










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


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Hi @danextian 
I have tried this but its still resulting the entire month value

  • CM Value =CALCULATE(DISTINCTCOUNT(Worksheet[Name]),DATESMTD(Dim_Calendar[Date]))
  • Employees SPLM = CALCULATE([CM Value],DATEADD(Dim_Calendar[Date],-1,MONTH))

You can see in my screenshot that it works. Did you filter only up to a specific date or did you select the whole monht?










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


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

  1. I have Date between slicer which ranges from 02-09-2024 to 04-01-2025
  2. The CM measure should show the values from 01-01-2025 to 04-01-2025
  3. The PM measure should show the sameperiod last month which is 01-12-2024 to 04-12-2024 but here is the issue this is not working as expected!!

  4. for the information the [Total Employees] measure is
    Total Employees=
    CALCULATE(DISTINCTCOUNT(Worksheet[Name]),DATESMTD(Dim_Calendar[Date])),here we are taking distinct count from the Name column 
AnkitKukreja
Super User
Super User

Hi! @Gokul_Saraboji 

 

I don't see your dax for PM. Also, a parallel period would not work here as it generates the entire previous month's dates.

You can try this - 


CM Employees =
CALCULATE(
[Total Employees],
DATESBETWEEN(
Dim_Calendar[Date],
MIN(Dim_Calendar[Date]),
MAX(Dim_Calendar[Date]) ) )

 

 

For Power BI trainings or support dm or reach out to me on LinkedIn.
If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904
https://topmate.io/ankit_kukreja
Karthik_KJ
Regular Visitor

= CALCULATE(SUM(table[column]), PREVIOUSMONTH('DateTime'[DateKey]))

 

bhanu_gautam
Super User
Super User

@Gokul_Saraboji , Try using

 

DAX
PM Employees =
CALCULATE(
[Total Employees],
DATESMTD(
PARALLELPERIOD(Dim_Calendar[Date], -1, MONTH)
)
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Hi @bhanu_gautam 
This is not working its just resulting the entire Previous Month values here!

@Gokul_Saraboji , Check this one and if it does not work share sample data

 

PM Employees =
CALCULATE(
[Total Employees],
DATESMTD(
DATEADD(Dim_Calendar[Date], -1, MONTH)
)
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






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! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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