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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago
I think:
Turover LYTD = CALCULATE(TOTALYTD(SUM(Turnover[turnover_sc])), SAMEPERIODLASTYEAR('Date'[Date]))
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))