Forum Discussion
Counting selected month at certain month values
Hi everyone!
I have a table with a row for each month and country, and a fake date that refers to the beginning of the month. I need a DAX formula to count the rows of the table (the months) that are selected by my slicers (year, month and country slicers), from the first month of the selection till the current one.
So, i.e. if I select Year: 2019, Month: March to July and a single country, I have to obtain this:
I also have a dimension date table related to this using the fake date. I use it to add information about month name, number etc.
I wrote this formulas using the dimDate to retrieve the fisrt day of the first month selected and the end of each month:
And they works fine (fist column is FirstDate and second is MonthEnd):
Using those measures, i tried to filter the table (or even a ALLEXCEPT of the table, keeping filtering on contries), choosing dates between FirstDate and MonthEnd, but it doesn't work.
Can someone help me whit this? Many thanks.
P.S. I already tried something like:
Check the screenshot, is this what you need. Please find formula
Cumm month = var _min = minx(ALLSELECTED('Date'),('Date'[Date])) return CALCULATE(DISTINCTCOUNT('Date'[Month-Year]),FILTER(all('Date'),'Date'[Date]<=max('Date'[Date])&& 'Date'[Date]>=_min))This calendar is attcahed with my facts.
3 Replies
- amitchandak
Super User
As long as your table is joined with date table and date table has month, year, month year you can filter on any range. Similarly, if you take a country Slicer, It will filter on that.
What is that you are not able to achieve, require a formula.
I need to show this count for every level of the "month name" variable in the dim date. If May is the third selected month it should be written 3 on the May row. (If one country is selected. If I select two countries of course it's 6).
For some reasons, it compute the count as 1 for each month (with one selected country), it seems like the table is keeping the filter from the selected month name, even if I used a calculate on a ALLEXCEPT where selected month is not considered.
- amitchandak
Check the screenshot, is this what you need. Please find formula
Cumm month = var _min = minx(ALLSELECTED('Date'),('Date'[Date])) return CALCULATE(DISTINCTCOUNT('Date'[Month-Year]),FILTER(all('Date'),'Date'[Date]<=max('Date'[Date])&& 'Date'[Date]>=_min))This calendar is attcahed with my facts.
