Does PBI expression can have sales for 2020 ?

[admin11]

Hi All

Does PBI allow us to create a expression that compute 2020 sales amount , i mean even user filter year=2021 , that expression will still display 2020 sales.

Paul

[PhilipTreacy]

@admin11 

Hi Paul,

I just realised that @v-deddai1-msft  already answered your question and I just posted the same thing.  My bad, this new forum layout is confusing.

Looking at the image you posted you are missing the ALL() in the measure. 

Just copy/paste this code rather than typing it out.

This is what should work and does work for me in your PBIX file

SALES_IN_2020 = CALCULATE( SUM(SALES[sales]),FILTER(ALL('Date'),'Date'[Year] = 2020))

Regards

Phil

[admin11]

Hi Philip

I think now i am convince that PBI is not allow to have expression that will not affected when user set filter . i mean if user set year filter =2019 , there is no way any expression can display 2020 sales amount.

Paul

[PhilipTreacy · Accepted solution]

@admin11 

Yes there is that's exactly what ALL is for, it removes any filters .

Here's a link to your PBIX file - check the Measure/Card I created.

regards

Phil

[PhilipTreacy]

@admin11 

Hi Paul,

Sorry I couldn't get the download from Dropbox, I kept getting an error.

To do what you want you need to use ALL('Date') to remove the slicer filters for Year

Try this

SALES_IN_2020 = CALCULATE( SUM(SALES[sales]),FILTER(ALL('Date'),'Date'[Year] = 2020))

Regards

Phil

[admin11]

Hi Phil

I just try , when i use your expression , when i select year =2019 , the sales amount dis-appear.

https://www.dropbox.com/s/nmyuuszuwdawcmb/PBT_V2021_127%20sales%20not%20affect%20by%20filter%20by%20...

Above link is my PBI file

Paul

[v-deddai1-msft]

Hi @admin11 ,

I can't get your pbix file from your link. Would you please share it again. 

There are many ways to filter data for 2020. You'd better have a date table , and you can set a slicer or page level filter.

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

Best Regards,

Dedmon Dai

[admin11]

Hi Deddai

https://www.dropbox.com/s/m5od756d792iy4b/PBT_V2021_131%20solve%20SALES%20EXPRESS%20GET%202020%20SAL...

Above is my PBI file

Paul

[v-deddai1-msft]

Hi @admin11 ,

You can fix filter in the formula:

SALES_ = CALCULATE( SUM(SALES[sales]),FILTER(all（'Date'）,'Date'[Year] = 2020))

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

Best Regards,

Dedmon Dai

[admin11]

Hi All

Your expression work fine , when i dont filter year ,  but when i select year = 2019 , it display blank.  what i need is even when user filter year=2021 , the display still can display 2020 sales amount.

[v-deddai1-msft]

Hi @admin11 ,

I have modified the original reply, please try again.

Best Regards,

Dedmon Dai

[PhilipTreacy]

 @admin11 

Hi Paul,

The short answer would be yes.

But need to see data to do it for you.

regards

Phil

[admin11]

Hi Phil

https://www.dropbox.com/s/lahhxwwqy272ix6/PBT_V2021_131%20solve%20SALES%20EXPRESS%20GET%202020%20SAL...

Above is my PBI file

Paul

[PhilipTreacy]

@admin11 

Hi Paul,

Sorry I've only just seen this. I didn't know you'd replied.

When you reply you need to @ mention people so they get notified.

When typing your reply just type the @ symbol and a list of names will appear for those who have responded in the thread.  Click on the one(s) you want to notify that you've replied.

I can't access your file, Dropbox says it's been deleted.

regards

Phil

[admin11]

@PhilipTreacy  

https://www.dropbox.com/s/m5od756d792iy4b/PBT_V2021_131%20solve%20SALES%20EXPRESS%20GET%202020%20SAL...

Above link is my PBI file

Paul