Forum Discussion
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 previous year net value.
Probably there are other specific functions to do these but I need the calculation to be done in DAX.
If the Max Year of the date slicer is 2024, when I use the following calculation it works:
Net Value = Calculate(
SUM(Sales[NetValue]),
Dates[Year] = 2023
)
BUT if I do this it doesn't work:
Net Value =
VAR previousYear = MAX(Dates[Year]) -1
RETURN
Calculate(
SUM(Sales[NetValue]),
Dates[Year] = previousYear
)
Can anyone help me?
pls try this
Net Value =
VAR previousYear = MAX(Dates[Year]) -1
RETURNCalculate(
SUM(Sales[NetValue]),FILTER(All(Dates),
Dates[Year] = previousYear)
)
or ------Net Value =
VAR previousYear = MAX(Dates[Year]) -1
RETURNCalculate(
SUM(Sales[NetValue]),FILTER(ALLSELECTED(Dates),
Dates[Year] = previousYear)
)
4 Replies
if you write it like this what do you get ?
Net Value =
VAR previousYear = MAX(Dates[Year]) -1
RETURN
previousYearI get 2023, which is the expected value since in the example the max is 2024
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
pls try this
Net Value =
VAR previousYear = MAX(Dates[Year]) -1
RETURNCalculate(
SUM(Sales[NetValue]),FILTER(All(Dates),
Dates[Year] = previousYear)
)
or ------Net Value =
VAR previousYear = MAX(Dates[Year]) -1
RETURNCalculate(
SUM(Sales[NetValue]),FILTER(ALLSELECTED(Dates),
Dates[Year] = previousYear)
)
