Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MxJ
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
v-jiascu-msft
Employee
Employee

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.

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.

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.

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.

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.

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.

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.

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

 

Hi @MxJ,

 

I'm afraid this is what it should be. Let's have a look. The 2019-January means 2019-Jan compares 2018-Jan. The 2019-Qtr 1 means 2019-Qtr 1 compares 2018-Qtr 1. Though the value of 2019-Qtr 1 only has one month, 2018-Qtr 1 has three months value. 

Then there is a question. How can we know that the 2019-February doesn't have data or it should be 0 (the result is 0)?

 

 

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.

Hi @v-jiascu-msft !

 

Sorry for my late response, I was on holiday. I guess you are right. I think I will exclude the current year and make a extra button "2019" that will go to a bookmark that shows the same table but with figures per month instead of per quarter, then the numbers do make sense.

 

If you have a better option though, please let me know! 🙂

 

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.