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 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?
 
 
 
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.

4 Replies

  • 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))
  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    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.