Last year sales measure when data is filtered with slicer

[RandomText]

Hi,

I have 2 tables. One is with sales data and another is Year table with distinct years.

Everything was fine when I needed just current year data and last year data. 

Now there's one sheet where data can be for 1 year, 2 years and so on. And another sheet where I need only current year (or highest year selected on slicer) with last year. 

The problem is when slicer is selected for example to 2019 I can get current year, but can't get last year sales because it filters table to 2019 only.

I'm using this formulas to get current year and last year:

CY: 

CALCULATE(SUMX(Statistics; Statistics[ST03020]; FILTER(Statistics; Statistics[ST03015].[Year]=[Year Slicer]))

LY: 

CALCULATE(SUMX(Statistics; Statistics[ST03020]; FILTER(Statistics; Statistics[ST03015].[Year]=[Year Slicer]-1))

So is there a way to get last year data and keep slicer?

I guess I could make a workaround with copy of statistics table and get data from it and do not connect it with year slicer, but then report size will grow double. 

[Icey]

Hi @RandomText ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards
Icey

[Anonymous]

Actually you don't have to connect the statistics table to the year table. If you delete the relationship with the Year table and the Statistics table, I think the measures will work fine.

[amitchandak · Accepted solution]

The filter should move to year table or date table

CALCULATE(SUMX(Statistics; Statistics[ST03020]); FILTER(all(Year); Year.[Year]=max([Year Slicer])-1))

In case you have date. Create a date calendar. and use following

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(dateadd('Date'[Date],-1,Year),-21,Day))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[RandomText]

Thanks @amitchandak !

It was so simple! I was trying to remove filter with ALL() from field I want to "unfilter", when I needed to remove from date table which filters that field. 🙂 

[Anonymous]

You need to create one date table which is disconnected with your main table.

In your case simple disconnect year table.

Add year column in slicer.

I assume there is year column in your main table.

Current year MEasure=If(max(table[year])=Selectevalue(year[year]),1,0)

add this to visual level filter and set it to 1.

Similarly for previous year 

Current year MEasure=If(max(table[year])=Selectevalue(year[year])-1,1,0)

Incase current+previous 

Current + previous year MEasure=If(max(table[year]) in {Selectevalue(year[year])-1,Selectevalue(year[year])),1,0)

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.