Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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:
Hi @Gokul_Saraboji ,
I'm so sorry for the trouble. I've unmarked the reply as the solution.
Best Regards,
Gao
Community Support Team
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
)
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
|
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 |
|
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 |
|
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:
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)
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
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 ) )
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.
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.
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!
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 ) )
Proud to be a Super User!
Hi @danextian
I have tried this but its still resulting the entire month value
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?
Proud to be a 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]) ) )
= CALCULATE(SUM(table[column]), PREVIOUSMONTH('DateTime'[DateKey]))
@Gokul_Saraboji , Try using
DAX
PM Employees =
CALCULATE(
[Total Employees],
DATESMTD(
PARALLELPERIOD(Dim_Calendar[Date], -1, MONTH)
)
)
Proud to be a Super User! |
|
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)
)
)
Proud to be a Super User! |
|
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
80 | |
65 | |
52 | |
49 |
User | Count |
---|---|
212 | |
89 | |
79 | |
68 | |
60 |