Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Get data from Last December when select data from slicer

Hey,

 

I have a model with two tables. A fact table and a date table. They are linked trhough date.

I need to show on a matrix the values per currency for last December when i select the actual year in a slicer.

 

I found this measure:

LastDecember =
var _selectedYear =SELECTEDVALUE(Date_Table[Year])-1
RETURN
CALCULATE(SUM('Fact_Table'[Values]),FILTER(ALL('Fact_Table'),YEAR('Fact_Table'[Date])=_selectedYear && MONTH('Fact_Table'[Date])=12)).
 
When i choose 2021 it gives the total amount of every currency in December 2020 for each line in the matrix instead of the value for that currency alone. Its looks like the data its not organized by the category that i put on the field "Lines" in the matrix.
 
Thanks in advance,
 
Rafael Sales



1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , in such case better to use a date table and use month, year etc from date table only

     

    if your Date_Table is joined with table then try like

    LastDecember =
    var _selectedYear =SELECTEDVALUE(Date_Table[Year])-1
    RETURN
    CALCULATE(SUM('Fact_Table'[Values]),FILTER(ALL('Date_Table'),YEAR('Date_Table'[Date])=_selectedYear && MONTH('Date_Table'[Date])=12))

     

     

    if not joined then remove all from table and try

     

    LastDecember =
    var _selectedYear =SELECTEDVALUE(Date_Table[Year])-1
    RETURN
    CALCULATE(SUM('Fact_Table'[Values]),FILTER(('Fact_Table'),YEAR('Fact_Table'[Date])=_selectedYear && MONTH('Fact_Table'[Date])=12)).