Forum Discussion

brysonb12's avatar
brysonb12
Frequent Visitor
3 years ago
Solved

Date Selection Slicer

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?

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.