Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dynamic year comparization

I want to create a report that has a dynamic year filtering which shows the year one choose (in a filter) and the previous year (selected year  - 1). 

 

I do now use this logic, which is not dynamic.

 

This year = IF('DateKey'[Date].[Year] = 'DateKey'[Today].[Year],1,0)

Last year = IF('DateKey'[Date].[Year] = ('DateKey'[Today].[Year]-1),1,0)

 

 

Then uses this to calculate sales for this year and last year. 

 

Total sales this year = CALCULATE('Total Sales Navision'[Total sales], FILTER(DateKey, DateKey[This year]=1))
Total sales last year = CALCULATE('Total Sales Navision'[Total sales], FILTER(DateKey, DateKey[Last year]=1))
 
However, I want some logic whics has "Total sales year n" and "Total sales year n-1" and then a filter were one can choose the year "n" as a filter. 
 
F ex if you choose 2020 in a dropdown - it will shows total sales for 2020 and total sales for 2019. 
 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous 

    I created a sample with some relatively simple data , hope it will help you .

    Original data :

    (1)Create a new table with [Date] from original table and display with year .Then add the new column in a slicer to filter data in original table.

    Date = SUMMARIZECOLUMNS('Table'[Date].[Year])

    (3)Create two measures to return the total sales from current year and previous year through the specified year in slicer .

    current year total sales = CALCULATE(SUM('Table'[value]),FILTER('Table',YEAR('Table'[Date])=SELECTEDVALUE('Date'[Year])))
    previos year total sales = CALCULATE(SUM('Table'[value]),FILTER('Table',YEAR('Table'[Date])=SELECTEDVALUE('Date'[Year])-1))

    The final result is as shown :

    I have attached my pbix file , you can refer to it .

     

    Best Regard

    Community Support Team _ Ailsa Tao

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

8 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Hi Anonymous ,

     

    You can create a two measure like below:-

    Total sales this year =
VAR _selectedVlaue =
    SELECTEDVALUE ( datekey[year] )
RETURN
    CALCULATE (
        SUM ( 'Total Sales Navision'[Total sales] ),
        FILTER ( DateKey, DateKey[year] = _selectedVlaue )
    )

     

    Total sales last year =
VAR _selectedVlaue =
    SELECTEDVALUE ( datekey[year] ) - 1
RETURN
    CALCULATE (
        SUM ( 'Total Sales Navision'[Total sales] ),
        FILTER ( DateKey, DateKey[year] = _selectedVlaue )
    )

     

    Thanks,

    Samarth

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for the answer, Samarth_18 

       

      Hence, when I entered the code - the "Total sales last year" did not work as expected. 

      Total sales last year (dynamic) = 
VAR _selectedVlaue =
    SELECTEDVALUE ( DateKey[Year] ) -1 

    RETURN
    CALCULATE('Total Sales Navision'[Total sales], 
    FILTER ( DateKey, DateKey[Year] = _selectedVlaue )

    )

       

      Did I do anything worng or do I have to do anything with my filter or something?

      • Samarth_18's avatar
        Samarth_18
        Community Champion
        4 years ago

        Anonymous , Try this:-

        Total sales last year (dynamic) =
VAR _selectedVlaue =
    SELECTEDVALUE ( DateKey[Year] ) - 1
RETURN
    CALCULATE (
        SUM ( 'Total Sales Navision'[Total sales] ),
        FILTER ( DateKey, DateKey[Year] = _selectedVlaue )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous 

    I created a sample with some relatively simple data , hope it will help you .

    Original data :

    (1)Create a new table with [Date] from original table and display with year .Then add the new column in a slicer to filter data in original table.

    Date = SUMMARIZECOLUMNS('Table'[Date].[Year])

    (3)Create two measures to return the total sales from current year and previous year through the specified year in slicer .

    current year total sales = CALCULATE(SUM('Table'[value]),FILTER('Table',YEAR('Table'[Date])=SELECTEDVALUE('Date'[Year])))
    previos year total sales = CALCULATE(SUM('Table'[value]),FILTER('Table',YEAR('Table'[Date])=SELECTEDVALUE('Date'[Year])-1))

    The final result is as shown :

    I have attached my pbix file , you can refer to it .

     

    Best Regard

    Community Support Team _ Ailsa Tao

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