YTD MEASURES AND FILTER CONTEXT THAT CHANGES BETWEEN MONTHS.

[bolabuga]

Hello everyone.
Its the first time im seeing this issue, never had crossed this kind of problem up till now.

I have a filter context as described:
1 - SLICER WITH "YEAR_MONTHY" (201604 is one of the choices, for example)
2 - SLICER WITH "CATEGORY SERVICE"
3 - MATRIX WITH "SUBCATEGORY SERVICE" FOLLOWED BY A YEAR TO DATE MEASURE THAT COUNTS THE NUMBER OF SUBCATEGORY SERVICES.

The problem is, on the following scenario: I have on january counts of subcategory A,B and C and on february i had "zero" count of service A. When selecting on the slicer the "year_month (201602)", the filter context applied show on the "matrix" the subcategory services "B" and "C", with the respectives correct year to date counts, and on the grand total its showing the correct grand total of everyone count.

Ok, i understand that on the grand total, the matrix filter is not happening, thus showing the correct valor. What i would like to know is, how can i bypass the context of the matrix and make it show the result of subcategory service "A", when selecting the "year_month" 201602.

[bolabuga]

any suggestion?

[CheenuSing · Accepted solution]

Hi @bolabuga

Beacuse the filter context is based on YearMonth  so only those records that match the YearMonth Slicer on the right side will be retrieved.

Try the following

1. Create a table called MonthTable = summarize ('SAMPLE',[YearMonth])

2. This should be a stand alone table and not connected to your SAMPLE table.

3. Make changes to your measures

   COUNT SUBCATEGORY = Calculate (COUNTA('SAMPLE'[SUBCATEGORY]),FILTER('SAMPLE','SAMPLE'[YEARMONTH] =MAX('Calendar'[YEARMONTH]) ))

4. YEAR TO DATE COUNT SUBCATEGORY = Calculate(COUNTA([SUBCATEGORY]),FILTER('SAMPLE','SAMPLE'[YEARMONTH] <=MAX('Calendar'[YEARMONTH]) ))

Now use the YearMonth from the MonthTable to create the slicer and the matrix preview table as already done.

If this works for you accept this as a solution and also give KUDOS.

Cheers

CheenuSing

[bolabuga]

Cheenu, thks.

Its realy nice solution, since using a unrelated table makes all the subcategory visible at all times, just what i needed. I tested the solution on the sample file, using the unrelated table on the slicer and it works as intented. I just need to leave an observation making clear that when theres no slicer selection, the subcategory measure will always show the current month.

[v-sihou-msft]

@bolabuga

I'm still not clear about your requirement.

Can you share some sample data and screenshots?

Regards,

[bolabuga]

@v-sihou-msft

hehe sorry, i posted initially from my work place and the forum features are not working from there. 

here is the link for a sample: https://drive.google.com/open?id=0B8Aq8DhGApJqX2VEWkIxY2VqVDA

and a screenshot where i tried to describe my problem.