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

Samarth_18's avatar
Samarth_18
Icon for Community Champion rankCommunity 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
      Icon for Community Champion rankCommunity 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

        It did, unfortunately, not do the trick. 

        (for clearification, Total sales = SUM(AmountNOK)