Forum Discussion

vivek_babu's avatar
vivek_babu
Icon for Helper II rankHelper II
1 year ago
Solved

Countrows not working properly

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. ...
Need Help
  • vivek_babu's avatar
    vivek_babu
    1 year ago

    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
Bibiano_Geraldo's avatar
Bibiano_Geraldo
Icon for Super User rankSuper User
1 year ago

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
    )
)