Forum Discussion

FilipeSantos_pt's avatar
FilipeSantos_pt
Helper I
2 years ago
Solved

Calculation with date table with previous date not working

I have a Date Table dimension (created in DAX) and a fact Sales table where there is a connection with Dates[Date] and Sales[Date]. I need to get the max Year on the date filter and show the previou...
General Comment
Need Help
  • Ahmedx's avatar
    Ahmedx
    2 years ago

    pls try this

    Net Value =

    VAR previousYear = MAX(Dates[Year]) -1
    RETURN

    Calculate(
    SUM(Sales[NetValue]),FILTER(All(Dates),
    Dates[Year] = previousYear)
    )
    or ------

    Net Value =

    VAR previousYear = MAX(Dates[Year]) -1
    RETURN

    Calculate(
    SUM(Sales[NetValue]),FILTER(ALLSELECTED(Dates),
    Dates[Year] = previousYear)
    )



Ahmedx's avatar
Ahmedx
Super User
2 years ago

if you write it like this what do you get ?

Net Value =
VAR previousYear = MAX(Dates[Year]) -1
RETURN
previousYear

FilipeSantos_pt's avatar
FilipeSantos_pt
Helper I
2 years ago

If I use that measure in a chart bar where the x-axis is the year, it returns properly the previous year for ech year 

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    pls try this

    Net Value =

    VAR previousYear = MAX(Dates[Year]) -1
    RETURN

    Calculate(
    SUM(Sales[NetValue]),FILTER(All(Dates),
    Dates[Year] = previousYear)
    )
    or ------

    Net Value =

    VAR previousYear = MAX(Dates[Year]) -1
    RETURN

    Calculate(
    SUM(Sales[NetValue]),FILTER(ALLSELECTED(Dates),
    Dates[Year] = previousYear)
    )