Forum Discussion
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. ...
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)RETURNCALCULATE(SUM('Table'[Sales]),'Table'[Year] = prev_year && 'Table'[Reporting Month Number] = prev_month)RegardsVivek N
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
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