Date Selection Slicer

[brysonb12]

Good Morning All!

Im looking for some help. I have a slicer that has Year and Month. What Im trying to do is filter my data based on the month I want to do my audit. 

I do monthly audits based on spend from previous months. 

For example - I am doing audits this month (December). My audit is based on the spend from September and October.

What I want to be able to do is in the slicer (Audit Months) is actaully selct the month it currently is "December" and it know that I want to pull September and October data. 

As follows when I select january, I want it to select October and December

Any one have suggetions?

[v-xinruzhu-msft · Accepted solution]

Hi @brysonb12 

You can create a new measure:

Measure 2 = var a=date(SELECTEDVALUE('Table 2'[Date].[Year]),MONTH(CONVERT(SELECTEDVALUE('Table 2'[Date].[Month]) & " 1",DATETIME) )-2,31)
var b=date(SELECTEDVALUE('Table 2'[Date].[Year]),MONTH(CONVERT(SELECTEDVALUE('Table 2'[Date].[Month]) & " 1",DATETIME) )-3,31)
var c=FILTER('Table',[sample date ]>=B&&[sample date ]<=A)
return IF(ISFILTERED('Table 2'[Date].[Month])=FALSE(),0,IF(COUNTROWS(c)>0,0,1))

Then put the measure to the visual filter:

But the condition is that the date table and the fact table cannot have a relationship:

Best Regards!

Yolo Zhu

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

[brysonb12]

Ocotber and November*****