Forum Discussion

MxJ's avatar
MxJ
Frequent Visitor
7 years ago

Filter on certain filter category

Hi Guys,

 

I am using a table that shows YTD wholesale growth with the following DAX-query:


Wholesale Growth = ([Total Wholesale]-Calculate([Total Wholesale];SAMEPERIODLASTYEAR(Real_date[Date])))/Calculate([Total Wholesale];SAMEPERIODLASTYEAR(Real_date[Date]))

 

This return the following results: 

 

The problem is that there is a year filter connected to the table. When selecting years 2015-2018 it works fine. But when selecting 2019 the results get messed up because for Q1 it returns for 2018 Jan/Feb/Mrt and for 2019 only Jan because that is all the data I have right now.

 

So what I want is that when selecting 2019 of the filter. Only 2018 january data is considered in the calculation, instead of all three months. This will empty the Q1(feb & mrt), Q2,  Q3 and Q4 data, keeping only data in Q1(Jan).

 

So in short: When year selected is 2019 only show January data of both 2018 and 2019, otherwise (all other years), show all data. 

 

Hopefully someone can help me! :)

Need Help

11 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi MxJ,

     

    Try this formula, please.

     

    Wholesale Growth =
IF (
    ISBLANK ( [Total Wholesale] );
    0;
    (
        [Total Wholesale]
            - CALCULATE ( [Total Wholesale]; SAMEPERIODLASTYEAR ( Real_date[Date] ) )
    )
        / CALCULATE ( [Total Wholesale]; SAMEPERIODLASTYEAR ( Real_date[Date] ) )
)

     

    Best Regards,

    • MxJ's avatar
      MxJ
      Frequent Visitor
      7 years ago

      I have tried it but unfortunatly it does not work. It sets Q2, 3, 4 on zero but still uses jan, feb, mar for Q1 2018 and only jan for Q1 2019.

    • MxJ's avatar
      MxJ
      Frequent Visitor
      7 years ago

      I have tried it but unfortunatly it does not work. It sets Q2, 3, 4 on zero but still uses jan, feb, mar for Q1 2018 and only jan for Q1 2019.

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        Hi MxJ,

         

        Can you share a small sample? Please mask the sensitive parts first.

        What's the formula of [Total Wholesale]?

         

        Best Regards,