Forum Discussion

Karthik12's avatar
Karthik12
Post Patron
3 years ago

Customized automatic filter of data

Dear Team,

Good evening !

 

I have daily routine dashboard which will be reviewed everyday morning from start of month till previous day production status.

In this I have plan table which has plan till end of the month and actual production table till yesterday seperately.

 

If I apply , fillter -> in this -> Month, then actual production date shown finely but Plan showing whole month data.

 

So, everyday I am manually adjusting the filter like below

Is there any possiblity to automate this filter so that manual adjustment can be avoided?

 

Need Help
Show and Tell

3 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    Hi Karthik12 

     

    You can create a measure as a filter. Add this measure to the visual you want to filter and set it to show items when the value is 1. 

    Measure = IF(SELECTEDVALUE('Date'[Date])>=DATE(YEAR(TODAY()),MONTH(TODAY()),1)&&SELECTEDVALUE('Date'[Date])<TODAY(),1,0)

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

      • v-jingzhang's avatar
        v-jingzhang
        Community Support
        3 years ago

        Hi Karthik12 

         

        Thank you for the sample pbix. It helps a lot. My previous measure doesn't work because the table visual doesn't have the 'Date'[Date] column used in the measure. To deal with it, instead of adding a date filter on the visual, you need to filter dates inside the calculation measures. You can create new measures as below and use them in the table visual. 

        New Plan = CALCULATE(SUM('Plan sheet'[plan]), 'dates'[Date] >= TODAY()-DAY(TODAY())+1 && 'dates'[Date] < TODAY())
        New Actual = CALCULATE(SUM('Production sheet'[Pro Qty]), 'dates'[Date] >= TODAY()-DAY(TODAY())+1 && 'dates'[Date] < TODAY())
        New Gap = 
 VAR _gap = SUMX( VALUES('type'[type]), [New Plan] - [New Actual]) 
 Return
   IF( _gap = 0, BLANK(), _gap )

        Best Regards,
        Community Support Team _ Jing
        If this post helps, please Accept it as Solution to help other members find it.