cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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: 

Knipsel2.PNG

 

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! 🙂

11 REPLIES 11
Highlighted
Microsoft
Microsoft

Re: Filter on certain filter category

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Filter on certain filter category

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.

Highlighted
Frequent Visitor

Re: Filter on certain filter category

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.

Highlighted
Microsoft
Microsoft

Re: Filter on certain filter category

Hi @MxJ,

 

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

What's the formula of [Total Wholesale]?

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Filter on certain filter category

date.PNGWholesale.PNG

On the left is my date table and on the right the wholesale table, the 2 are linked on Date = Sale_Date.

 

The calculation for Total Wholesale

Total Wholesale = calculate(sum('Wholesale'[Wholesale]);'Wholesale'[Segment]<>"3. Other")

It sums the wholesale column and excludes one segment.

Highlighted
Microsoft
Microsoft

Re: Filter on certain filter category

Hi @MxJ,

 

Please download a demo from the attachment. All the date like fields should from the Real_Date table.

Filter-on-certain-filter-category

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Filter on certain filter category

Thanks for your reply. 

 

The only problem I have is that the formulas dont work when I use the real_date table is steady of the date from the wholesale table.. It returns "empty" when i use:

Total Wholesale YTD = TOTALYTD([Total Wholesale];'Real_date'[Date]) 

Also,  the figure from januari still does not show at Q1. 2019 gives -0,90 while januari gives 0,32 in your example.

 

I need Q1 and 2019 to say 0,32 as well.

Highlighted
Microsoft
Microsoft

Re: Filter on certain filter category

Hi @MxJ,

 

Can you share a similar sample like mine? Please mask the sensitive parts first.

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Filter on certain filter category

My data is the same as your sample. The problem is that I need 2019(-0.9) and Qtr 1(-0.57) to be 0.32,, just like Januari. It sets Februari and march at 0 when calculating their own value but still take them into account when calculating 2019 and Qtr 1.ok.png

 

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors