Countrows not working properly

[vivek_babu]

Hi,

I am trying to count the rows based on the year and month column selected in the slicer. When a user selects Year = 2022 and Month = Jan then i need to count the rows for the previous month. In this case i need to count the rows for year = 2021 and month = Dec. I tried below logic but it is giving me blanks for all Jan. Can someone help me to fix the issue?

Screenshot:

Measure = 

var r_year = SELECTEDVALUE(PA_TOOL_COMPLIANCE_WINDOWS[Reporting Year_windows])

var r_month = SELECTEDVALUE(PA_TOOL_COMPLIANCE_WINDOWS[Reporting Month_windows])

return

IF(

    r_month ="Jan",

CALCULATE(COUNTROWS(BLADE_LOGIC_FIM),

FILTER(BLADE_LOGIC_FIM,BLADE_LOGIC_FIM[Reporting Year_fim] = r_year - 1 &&

BLADE_LOGIC_FIM[Reporting Month_fim] ="Dec")),

0

)

@Jai-Rathinavel @lbendlin @DataNinja777 @FreemanZ @Greg_Deckler @shafiz_p @Ritaf1983 @Kedar_Pande @Anonymous @Anonymous @vojtechsima 

Regards

Vivek N

[vivek_babu · Accepted solution]

Hi All,

The issue was with the filter function inside the calculate function. I removed the filter and directly applied the condition and it worked. Thanks for your help!

Prev_Sales =

Var r_year = SELECTEDVALUE('Table'[Year])

var r_month = SELECTEDVALUE('Table'[Reporting Month Number])

VAR prev_month = IF(r_month = 1, 12, r_month - 1)

VAR prev_year = IF(r_month = 1, r_year - 1, r_year)

RETURN

CALCULATE(SUM('Table'[Sales]),

'Table'[Year] = prev_year && 'Table'[Reporting Month Number] = prev_month)

Regards

Vivek N

[Anonymous]

Hi @vivek_babu 

Glad to know that you found the cause of the problem and solved it.
Thank you for sharing your solution. Please consider accepting your reply as a solution that will benefit other users experiencing similar problem.

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

[vivek_babu]

Hi @Anonymous ,

Yes i did! Thank You!

Regards

Vivek N

[vivek_babu]

Hi,

Please find the below sample data, Expectation is that the when user selects the year and month in the slicer then it needs to show the previous month sales. This is just an example i created to mimic my original requirement.

Current Logic:

Prev_Sales =

Var r_year = SELECTEDVALUE('Table'[Year])

var r_month = SELECTEDVALUE('Table'[Month])

VAR previous_month =

SWITCH(

r_month,

"Jan", "Dec",

"Feb", "Jan",

"Mar", "Feb",

"Apr", "Mar",

"May", "Apr",

"Jun", "May",

"Jul", "Jun",

"Aug", "Jul",

"Sep", "Aug",

"Oct", "Sep",

"Nov", "Oct",

"Dec", "Nov"

)

VAR previous_year =

IF(r_month = "Jan", r_year - 1, r_year)

RETURN

CALCULATE(SUM('Table'[Sales]),

FILTER('Table','Table'[Year] = previous_year && 'Table'[Month] = previous_month))
This is giving me blanks as result not sure why the filtering is not happening properly 

Sample Data,

Year	Month	Sales
2024	Jan	100
2024	Feb	200
2024	Mar	300
2024	Apr	400
2024	May	500
2024	Jun	600
2024	Jul	700
2024	Aug	800
2024	Sep	900
2024	Oct	1000
2024	Nov	1100
2023	Jan	50
2023	Feb	60
2023	Mar	70
2023	Apr	80
2023	May	90
2023	Jun	100
2023	Jul	1200
2023	Aug	1300
2023	Sep	1400
2023	Oct	1500
2023	Nov	1600
2023	Dec	1700
2022	Jan	2000
2022	Feb	3000
2022	Mar	4000
2022	Apr	5000
2022	May	6000
2022	Jun	7000
2022	Jul	8000
2022	Aug	9000
2022	Sep	10000
2022	Oct	11000
2022	Nov	12000
2022	Dec	13000
2021	Jan	20000

@Bibiano_Geraldo @shafiz_p @Kedar_Pande @lbendlin @Greg_Deckler @Jai-Rathinavel @FreemanZ @DataNinja777 @Ritaf1983 @Anonymous @Anonymous @vojtechsima 

Regards

Vivek N

[vivek_babu]

Hi @Bibiano_Geraldo @shafiz_p @Kedar_Pande 

Thanks for providing your solutions. Unfortunately, all the solution is giving me blank values. In all of three solutions the previous year and previous month value is coming correctly but when we countrows of the table by filtering the table's year and month with our previous year and previous month values is not working. Not sure why it is not filtering the data properly 

@Jai-Rathinavel @lbendlin @DataNinja777 @FreemanZ @Greg_Deckler @shafiz_p @Ritaf1983 @Kedar_Pande @v-jialongy-msft @v-xianjtan-msft @vojtechsima

Regards

Vivek N

[Bibiano_Geraldo]

Hi @vivek_babu ,
Create a calculated column for Month number using DAX bellow, if you already have one, just skip this step:

Reporting Month Number = 
SWITCH(
    PA_TOOL_COMPLIANCE_WINDOWS[Reporting Month_windows],
    "Jan", 1,
    "Feb", 2,
    "Mar", 3,
    "Apr", 4,
    "May", 5,
    "Jun", 6,
    "Jul", 7,
    "Aug", 8,
    "Sep", 9,
    "Oct", 10,
    "Nov", 11,
    "Dec", 12
)

Now youu can create a measure to calculate previous month total by this DAX:

Measure = 
VAR r_year = SELECTEDVALUE(PA_TOOL_COMPLIANCE_WINDOWS[Reporting Year_windows])
VAR r_month = SELECTEDVALUE(PA_TOOL_COMPLIANCE_WINDOWS[Reporting Month Number_windows]) -- Coluna com números para meses (1 = Jan, 12 = Dec)
VAR prev_month = IF(r_month = 1, 12, r_month - 1)
VAR prev_year = IF(r_month = 1, r_year - 1, r_year)
RETURN
CALCULATE(
    COUNTROWS(BLADE_LOGIC_FIM),
    FILTER(
        BLADE_LOGIC_FIM,
        BLADE_LOGIC_FIM[Reporting Year_fim] = prev_year &&
        BLADE_LOGIC_FIM[Reporting Month Number_fim] = prev_month
    )
)

[shafiz_p]

Hi @vivek_babu  Try this:

Measure = 
var r_year = SELECTEDVALUE(PA_TOOL_COMPLIANCE_WINDOWS[Reporting Year_windows])
var r_month = SELECTEDVALUE(PA_TOOL_COMPLIANCE_WINDOWS[Reporting Month_windows])
var prev_date = EOMONTH(DATE(r_year, MONTH(DATEVALUE("1 " & r_month & " " & r_year)), 1), -1)
var prev_year = YEAR(prev_date)
var prev_month = FORMAT(prev_date, "MMM")
return
CALCULATE(
    COUNTROWS(BLADE_LOGIC_FIM),
    FILTER(
        BLADE_LOGIC_FIM,
        BLADE_LOGIC_FIM[Reporting Year_fim] = prev_year &&
        BLADE_LOGIC_FIM[Reporting Month_fim] = prev_month
    )
)

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[Kedar_Pande]

@vivek_babu 

Updated Measure:
Measure = 
VAR r_year = SELECTEDVALUE(PA_TOOL_COMPLIANCE_WINDOWS[Reporting Year_windows])
VAR r_month = SELECTEDVALUE(PA_TOOL_COMPLIANCE_WINDOWS[Reporting Month_windows])
VAR previous_month =
SWITCH(
r_month,
"Jan", "Dec",
"Feb", "Jan",
"Mar", "Feb",
"Apr", "Mar",
"May", "Apr",
"Jun", "May",
"Jul", "Jun",
"Aug", "Jul",
"Sep", "Aug",
"Oct", "Sep",
"Nov", "Oct",
"Dec", "Nov"
)
VAR previous_year =
IF(r_month = "Jan", r_year - 1, r_year)
RETURN
CALCULATE(
COUNTROWS(BLADE_LOGIC_FIM),
FILTER(
BLADE_LOGIC_FIM,
BLADE_LOGIC_FIM[Reporting Year_fim] = previous_year &&
BLADE_LOGIC_FIM[Reporting Month_fim] = previous_month
)
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn