Date slicer that only effects last year

[tktmastr]

Hello,

I have a bar chart visual that compares this year's current sales to last year, but I can't seem to get the data how I want it.

I would like a total of current net revenue this year, and a total of net revenue last year as of the same calendar date (and have a date slicer that ONLY changes the 2019 data). I've attached a screenshot to show how I would like it to look - if I set the date slicer to a 2019 date the 2020 data doesn't show at all (which makes sense, but not sure how to break it out so that the 2019 data can be variable, but not 2020)

Hope that makes sense.

Thanks!

[v-frfei-msft]

Hi @tktmastr ,

Does that meet your requirement using If condition as below.

Measure = 
VAR maxyear =
    CALCULATE ( MAX ( 'date'[year] ), ALL ( 'date' ) )
VAR lastyear = maxyear - 1
VAR coun =
    CALCULATE ( DISTINCTCOUNT ( 'date'[year] ), ALLSELECTED ( 'date' ) )
RETURN
    IF (
        MAX ( 'date'[year] ) = lastyear
            && coun = 1,
        SUM ( 'Table'[revenue] ),
        CALCULATE ( SUM ( 'Table'[revenue] ), ALLEXCEPT ( 'date', 'date'[year] ) )
    )

[tktmastr]

Sorry, not sure that I am explaining it correctly. 

I have events in the year 2019, and events in 2020. For 2019 events, sales could have happened in 2018 or 2019, and for 2020 events, there could have been sales in 2019 and 2020. 

For the 2020 events, I want to see all previous sales, but for 2019 events I only want to see sales up to this point last year (but NOT starting at January 1 as there may have been sales in previous year).

I made a sample Power BI file if that helps: https://drive.google.com/open?id=1H5Ps9D8JxK6tzdxiSTdx3OrlgC-Zuo7y

[amitchandak · Accepted solution]

Not sure it is still create. But in case of 2019 , you want to get till Jan only and it 2020 jan then

Till date sales = 
var _date = date(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))
return
//_date

CALCULATE(SUM('Sales Data'[Net Price]),FILTER('Sales Data',(MAXX('Event Data','Event Data'[Event Year])<=2019 && 'Sales Data'[Date Bought]<=_date) || MAXX('Event Data','Event Data'[Event Year])>=2020))

This will not help at GT level as it will again be calculated and mac event yeat is 2020. For that we want at GT level then we need group first at year level or use hasonefilter

Link:https://www.dropbox.com/s/tcpc32l3cax4nto/Sample%20File17jan.pbix?dl=0

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 Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[amitchandak]

Not sure what do mean by making 2019 variable. Means do you want fix the 2020 and change rest by slicer. Make sure you have date calendar.

2020 = CALCULATE(SUM(Sales[Sales Amount]),all('Date'),year('Date'[Date]))=2020)

control as last year (till date)
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year)))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year))))
control as this year
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date])))

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 Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601