Use selected filter for previous year

[Phoenix538]

Good day all,

I am pretty new to Power Bi, loveing the program but stumbling accross something that I cannot figure out.

I sccuesfully managed to calculate the YTD turnover and compare it to the LYTD turnover. 

My report has a filter on year. So now I have 2020 selected and I want to see the comparison with previous year. 

I got it working with the LYTD using the following calculations:

Total Sales $ YTD = TOTALYTD(SUM(Turnover[turnover_sc]),'Date'[Date])

Total Sales $ LYTD = CALCULATE([Total Sales $ YTD], SAMEPERIODLASTYEAR('Date'[Date]))

But I also want to show the total turnover of previous year.

How can I calculate this? And How can i make a line diagram where the current year will continue in a straight line (I guess Running total)

But the turnover of previus year should be filled in for all months and not stop at April last year?

[V-lianl-msft · Accepted solution]

Hi @Phoenix538 ,

You can try to create an unrelated calendar table to use as a slicer.

Running total:

Running total= 
CALCULATE (
    SUM ( Sales[Sales] ),
    FILTER (
        ALLSELECTED ( 'Sales' ),
       SELECTEDVALUE(DimDate[year])= YEAR(Sales[Date])&&
       Sales[Date]<=MAX(Sales[Date])
    )
)

Sum of last year:

sum_Sales_last_year = 
CALCULATE (
    SUM( Sales[Sales] ),
    FILTER (
        ALL ( Sales[Date] ),
        YEAR(Sales[Date]) = SELECTEDVALUE(DimDate[year])-1
     ))

Sample .pbix

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

[Greg_Deckler]

I think:
Turover LYTD = CALCULATE(TOTALYTD(SUM(Turnover[turnover_sc])), SAMEPERIODLASTYEAR('Date'[Date]))

[Phoenix538]

Hi @Greg ,

Thank you for the reply, but the LYTD i already have. However I am looking to have the total tunover of the year 2019 and not only to date. 

I tried with 

Total Sales $ LY = CALCULATE([Total Sales $ YTD], Filter(ALL('Date'), 'Date'[Year]=MAX('Date'[Year])-1), SAMEPERIODLASTYEAR('Date'[Date]))

However this will filter out the results of 2019 because it is filtered on the year 2020.

(Hope it makes a little sense)

[amitchandak]

@Phoenix538 , Try the complete one if you looking for the full year

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))