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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
manojk_pbi
Helper III
Helper III

Help required to write DAX for % Change without considering filter

Hi 

 

I am writing a meaure to calculate %Change from Dec'23 to Mar'24 and display value on card. Using the below measure.

Along with this KPI, i have few more KPIs on the same report which will use the filter of the ReportingMonth. 

 

I want below expression should not filter based on selected reporting month and do the calculation just by using reporting month value and defined values from preMY.

 

%Change_CYCM =
VAR _preMY = VALUE("2023-12-01")
VAR _preqty =
CALCULATE (
SUM ( 'Bugs'[BUGS_COUNT] ), FILTER(ALLSELECTED('Bugs'),
'Bugs'[ReportMonth]=_preMY)
)
VAR _curMY = VALUE("2024-03-01")

VAR _curqty =
CALCULATE (
SUM ( 'Bugs'[BUGS_COUNT] ),
FILTER (
ALLSELECTED ( 'Bugs' ),

'Bugs'[ReportMonth] = _curMY
)
)
RETURN

DIVIDE ( _preqty-_curqty, _preqty )

1 ACCEPTED SOLUTION
v-nuoc-msft
Community Support
Community Support

Hi @manojk_pbi 

 

@some_bih Thank you very much for your prompt reply, and here allow me to offer some suggestions.

 

Based on your screenshot, I noticed that ReportMonth does not appear to be a date type of data.

 

vnuocmsft_0-1714026190400.png

 

Could you please check the field type and make sure to ensure that it is a date type.

 

Here I define it as short date.

 

vnuocmsft_1-1714026295381.png

 

Create a measure.

 

%Change_CYCM = 
var _preMY = DATE(2023, 12, 01)
VAR _preqty = 
    CALCULATE(
        SUM('Bugs'[BUGS_COUNT]),
        FILTER(
            ALL('Bugs'),
            'Bugs'[ReportMonth] = _preMY
        )
    )
var _curMY = DATE(2024,03, 01)
VAR _curqty = 
    CALCULATE(
        SUM('Bugs'[BUGS_COUNT]),
        FILTER(
            ALL('Bugs'),
            'Bugs'[ReportMonth] = _curMY
        )
    )
RETURN 
    DIVIDE( _preqty - _curqty , _preqty)

 

Here is the result.

 

vnuocmsft_2-1714026482947.png

 

vnuocmsft_3-1714026496918.png

Regards,

Nono Chen

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

View solution in original post

4 REPLIES 4
v-nuoc-msft
Community Support
Community Support

Hi @manojk_pbi 

 

@some_bih Thank you very much for your prompt reply, and here allow me to offer some suggestions.

 

Based on your screenshot, I noticed that ReportMonth does not appear to be a date type of data.

 

vnuocmsft_0-1714026190400.png

 

Could you please check the field type and make sure to ensure that it is a date type.

 

Here I define it as short date.

 

vnuocmsft_1-1714026295381.png

 

Create a measure.

 

%Change_CYCM = 
var _preMY = DATE(2023, 12, 01)
VAR _preqty = 
    CALCULATE(
        SUM('Bugs'[BUGS_COUNT]),
        FILTER(
            ALL('Bugs'),
            'Bugs'[ReportMonth] = _preMY
        )
    )
var _curMY = DATE(2024,03, 01)
VAR _curqty = 
    CALCULATE(
        SUM('Bugs'[BUGS_COUNT]),
        FILTER(
            ALL('Bugs'),
            'Bugs'[ReportMonth] = _curMY
        )
    )
RETURN 
    DIVIDE( _preqty - _curqty , _preqty)

 

Here is the result.

 

vnuocmsft_2-1714026482947.png

 

vnuocmsft_3-1714026496918.png

Regards,

Nono Chen

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

some_bih
Super User
Super User

Hi @manojk_pbi without model and details it is hard to spot issue. Still, try v2

%Change_CYCM v2=
VAR _preMY = DATE(2023,1,12) 
 
VAR _preqty =
CALCULATE (
SUM ( 'Bugs'[BUGS_COUNT] ), 
FILTER('Bugs',
'Bugs'[ReportMonth]=_preMY)
)
VAR _curMY = DATE(2024,1,3) 
 
VAR _curqty =
CALCULATE (
SUM ( 'Bugs'[BUGS_COUNT] ),
FILTER (
'Bugs' ,
'Bugs'[ReportMonth] = _curMY
)
)
RETURN
 
DIVIDE ( _preqty-_curqty, _preqty )




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

Proud to be a Super User!






Thanks for your reply. It is not working. For better understanding here i am attaching sample and current data model image.

 

manojk_pbi_0-1713795930854.png

Bugs data

BUG_TYPESum of BUGS_COUNTDATEPROJECT_NAMEReportMonth
Deferred3834Jan 24Project1Dec 23
L4128Jan 24Project1Dec 23
Deferred3826Feb 24Project1Jan 24
L4123Feb 24Project1Jan 24
Deferred3875Mar 24Project1Feb 24
L4121Mar 24Project1Feb 24
Deferred3950Apr 24Project1Mar 24
L4119Apr 24Project1Mar 24
Deferred269Jan 24Project2Dec 23
L44Jan 24Project2Dec 23
Deferred239Feb 24Project2Jan 24
L40Feb 24Project2Jan 24
Deferred235Mar 24Project2Feb 24
L40Mar 24Project2Feb 24
Deferred243Apr 24Project2Mar 24
L40Apr 24Project2Mar 24
Deferred366Jan 24Project3Dec 23
L418Jan 24Project3Dec 23
Deferred367Feb 24Project3Jan 24
L415Feb 24Project3Jan 24
Deferred382Mar 24Project3Feb 24
L414Mar 24Project3Feb 24
Deferred358Apr 24Project3Mar 24
L413Apr 24Project3Mar 24

Calender Table

MonthYearYearQuarterMonthDay
1/1/2023 0:002023Qtr 1January1
2/1/2023 0:002023Qtr 1February1
3/1/2023 0:002023Qtr 1March1
4/1/2023 0:002023Qtr 2April1
5/1/2023 0:002023Qtr 2May1
6/1/2023 0:002023Qtr 2June1
7/1/2023 0:002023Qtr 3July1
8/1/2023 0:002023Qtr 3August1
9/1/2023 0:002023Qtr 3September1
10/1/2023 0:002023Qtr 4October1
11/1/2023 0:002023Qtr 4November1
12/1/2023 0:002023Qtr 4December1
1/1/2024 0:002024Qtr 1January1
2/1/2024 0:002024Qtr 1February1
3/1/2024 0:002024Qtr 1March1
4/1/2024 0:002024Qtr 2April1

Thanks for your reply. It is not working. For better understanding here i am attaching sample and current data model image.

 

manojk_pbi_0-1713795930854.png

Bugs data

BUG_TYPESum of BUGS_COUNTDATEPROJECT_NAMEReportMonth
Deferred3834Jan 24Project1Dec 23
L4128Jan 24Project1Dec 23
Deferred3826Feb 24Project1Jan 24
L4123Feb 24Project1Jan 24
Deferred3875Mar 24Project1Feb 24
L4121Mar 24Project1Feb 24
Deferred3950Apr 24Project1Mar 24
L4119Apr 24Project1Mar 24
Deferred269Jan 24Project2Dec 23
L44Jan 24Project2Dec 23
Deferred239Feb 24Project2Jan 24
L40Feb 24Project2Jan 24
Deferred235Mar 24Project2Feb 24
L40Mar 24Project2Feb 24
Deferred243Apr 24Project2Mar 24
L40Apr 24Project2Mar 24
Deferred366Jan 24Project3Dec 23
L418Jan 24Project3Dec 23
Deferred367Feb 24Project3Jan 24
L415Feb 24Project3Jan 24
Deferred382Mar 24Project3Feb 24
L414Mar 24Project3Feb 24
Deferred358Apr 24Project3Mar 24
L413Apr 24Project3Mar 24

Calender Table

MonthYearYearQuarterMonthDay
1/1/2023 0:002023Qtr 1January1
2/1/2023 0:002023Qtr 1February1
3/1/2023 0:002023Qtr 1March1
4/1/2023 0:002023Qtr 2April1
5/1/2023 0:002023Qtr 2May1
6/1/2023 0:002023Qtr 2June1
7/1/2023 0:002023Qtr 3July1
8/1/2023 0:002023Qtr 3August1
9/1/2023 0:002023Qtr 3September1
10/1/2023 0:002023Qtr 4October1
11/1/2023 0:002023Qtr 4November1
12/1/2023 0:002023Qtr 4December1
1/1/2024 0:002024Qtr 1January1
2/1/2024 0:002024Qtr 1February1
3/1/2024 0:002024Qtr 1March1
4/1/2024 0:002024Qtr 2April1

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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