Help required to write DAX for % Change without considering filter

[manojk_pbi]

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 )

[v-nuoc-msft · Accepted solution]

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.

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.

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.

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]

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 )

[manojk_pbi]

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

Bugs data

BUG_TYPE	Sum of BUGS_COUNT	DATE	PROJECT_NAME	ReportMonth
Deferred	3834	Jan 24	Project1	Dec 23
L4	128	Jan 24	Project1	Dec 23
Deferred	3826	Feb 24	Project1	Jan 24
L4	123	Feb 24	Project1	Jan 24
Deferred	3875	Mar 24	Project1	Feb 24
L4	121	Mar 24	Project1	Feb 24
Deferred	3950	Apr 24	Project1	Mar 24
L4	119	Apr 24	Project1	Mar 24
Deferred	269	Jan 24	Project2	Dec 23
L4	4	Jan 24	Project2	Dec 23
Deferred	239	Feb 24	Project2	Jan 24
L4	0	Feb 24	Project2	Jan 24
Deferred	235	Mar 24	Project2	Feb 24
L4	0	Mar 24	Project2	Feb 24
Deferred	243	Apr 24	Project2	Mar 24
L4	0	Apr 24	Project2	Mar 24
Deferred	366	Jan 24	Project3	Dec 23
L4	18	Jan 24	Project3	Dec 23
Deferred	367	Feb 24	Project3	Jan 24
L4	15	Feb 24	Project3	Jan 24
Deferred	382	Mar 24	Project3	Feb 24
L4	14	Mar 24	Project3	Feb 24
Deferred	358	Apr 24	Project3	Mar 24
L4	13	Apr 24	Project3	Mar 24

Calender Table

MonthYear	Year	Quarter	Month	Day
1/1/2023 0:00	2023	Qtr 1	January	1
2/1/2023 0:00	2023	Qtr 1	February	1
3/1/2023 0:00	2023	Qtr 1	March	1
4/1/2023 0:00	2023	Qtr 2	April	1
5/1/2023 0:00	2023	Qtr 2	May	1
6/1/2023 0:00	2023	Qtr 2	June	1
7/1/2023 0:00	2023	Qtr 3	July	1
8/1/2023 0:00	2023	Qtr 3	August	1
9/1/2023 0:00	2023	Qtr 3	September	1
10/1/2023 0:00	2023	Qtr 4	October	1
11/1/2023 0:00	2023	Qtr 4	November	1
12/1/2023 0:00	2023	Qtr 4	December	1
1/1/2024 0:00	2024	Qtr 1	January	1
2/1/2024 0:00	2024	Qtr 1	February	1
3/1/2024 0:00	2024	Qtr 1	March	1
4/1/2024 0:00	2024	Qtr 2	April	1

[manojk_pbi]

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

Bugs data

BUG_TYPE	Sum of BUGS_COUNT	DATE	PROJECT_NAME	ReportMonth
Deferred	3834	Jan 24	Project1	Dec 23
L4	128	Jan 24	Project1	Dec 23
Deferred	3826	Feb 24	Project1	Jan 24
L4	123	Feb 24	Project1	Jan 24
Deferred	3875	Mar 24	Project1	Feb 24
L4	121	Mar 24	Project1	Feb 24
Deferred	3950	Apr 24	Project1	Mar 24
L4	119	Apr 24	Project1	Mar 24
Deferred	269	Jan 24	Project2	Dec 23
L4	4	Jan 24	Project2	Dec 23
Deferred	239	Feb 24	Project2	Jan 24
L4	0	Feb 24	Project2	Jan 24
Deferred	235	Mar 24	Project2	Feb 24
L4	0	Mar 24	Project2	Feb 24
Deferred	243	Apr 24	Project2	Mar 24
L4	0	Apr 24	Project2	Mar 24
Deferred	366	Jan 24	Project3	Dec 23
L4	18	Jan 24	Project3	Dec 23
Deferred	367	Feb 24	Project3	Jan 24
L4	15	Feb 24	Project3	Jan 24
Deferred	382	Mar 24	Project3	Feb 24
L4	14	Mar 24	Project3	Feb 24
Deferred	358	Apr 24	Project3	Mar 24
L4	13	Apr 24	Project3	Mar 24

Calender Table

MonthYear	Year	Quarter	Month	Day
1/1/2023 0:00	2023	Qtr 1	January	1
2/1/2023 0:00	2023	Qtr 1	February	1
3/1/2023 0:00	2023	Qtr 1	March	1
4/1/2023 0:00	2023	Qtr 2	April	1
5/1/2023 0:00	2023	Qtr 2	May	1
6/1/2023 0:00	2023	Qtr 2	June	1
7/1/2023 0:00	2023	Qtr 3	July	1
8/1/2023 0:00	2023	Qtr 3	August	1
9/1/2023 0:00	2023	Qtr 3	September	1
10/1/2023 0:00	2023	Qtr 4	October	1
11/1/2023 0:00	2023	Qtr 4	November	1
12/1/2023 0:00	2023	Qtr 4	December	1
1/1/2024 0:00	2024	Qtr 1	January	1
2/1/2024 0:00	2024	Qtr 1	February	1
3/1/2024 0:00	2024	Qtr 1	March	1
4/1/2024 0:00	2024	Qtr 2	April	1