Forum Discussion

Phoenix538's avatar
Phoenix538
Frequent Visitor
6 years ago
Solved

Use selected filter for previous year

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...
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    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.

Phoenix538's avatar
Phoenix538
Frequent Visitor
6 years ago

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's avatar
amitchandak
Super User
6 years ago

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