Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Filter controls on KPI Boxes regarding Current Month, Quarter, Fiscal Year revenue

Hi everyone I have three filters. Month, Quarter and Year that cover 3 years.

 

I also have three KPI Boxes that work with the filters. They show current month Revenue, current quarter revenue, and lastly current fiscal year revenue.

 

When no filter is selected, all three kpi boxes show different values. When I filter by fiscal year it shows that fiscal years values with recent month being my year end (oct 31), quarter shows q4, and fiscal year shows the sum of all 4 quarters.

However, when I filter by quarter, the quarter and fiscal year revenue KPI box's match and when I filter by month all three kpi boxes have the same value.

 

My goal is when I filter by 2019q3 in the quarter filter, for example, i want the the Fiscal year revenue kpi box shows me sum between q1-q3.  When I filter by month I want that months Quarter revenue in the fiscal quarter revenue kpi box and in the Year Revenue box I want YTD Value.

 

Pretty new to Dax anyone know how I can achieve this.

 

My second option is to use DAX to blank out certain kpis depending on the filter I use. For example if I use fiscal year filter nothing blanks. If I filter by quarter I only see current month and quarter kpi box with fiscal year kpi box blanked. Lastly if I filter by month blank the quarter/fiscal year kpi boxes. I have a dax code that im trying to play around with but no luck yet. 

Quarter Select = CALCULATE(IF(ISCROSSFILTERED(REVENUE_SUMMARY[FiscalQuarter]),1,0),ALLSELECTED(REVENUE_SUMMARY)) this is what im using

 

I am hoping that someone can help me figure this out

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    We create a sample and we can use three measures to meet your requirement.

     

    1. We need to create a date table and there is no relationship between date table and fact table.

    We create the slicers based on the date table.

     

    Date = ADDCOLUMNS(CALENDAR("2019/1/1","2020/12/31"),"Year",YEAR([Date]),"Q",YEAR([Date])&""&"Q"&""&QUARTER([Date]),"month",MONTH([Date]),"year&Month",YEAR([Date])*100 + MONTH([Date]))

     

     

    2. Then we need to create a year&month column in fact table.

     

    Year&month = YEAR('Table'[Date])*100 + MONTH('Table'[Date])

     

     

    3. Then we can create three measures.

     

    last month = 
var _lastmonth = MAX('Date'[year&Month])
var _isfilter = CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[Year&month]=_lastmonth))
return
_isfilter

     

    last quarter = 
var _selectmonth = SELECTEDVALUE('Date'[month])
var _lastQ = MAX('Date'[Q])
var _isfilter = CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[quarter]=_lastQ))
var _notfilter = CALCULATE(SUM('Table'[value]),FILTER('Table','Table'[quarter]=_lastQ && 'Table'[Month]=_selectmonth))
return
IF(
    ISFILTERED('Date'[month]),_notfilter,_isfilter)

     

    last year = 
var _lastY = MAX('Date'[Date])
var _isfilter = CALCULATE(SUM('Table'[value]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=_lastY))
return
_isfilter

     

    The result like this,

     

     

     

     

    If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

     

    It will be helpful if you can show us the exact expected result based on the tables.

    Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

     

    Best regards,

     

    Community Support Team _ zhenbw

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

     

    BTW, pbix as attached.