Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

FIlter Line chart DAX Formula by 1 year

I have a DAX Measure as shown below

SPLY INVoiced = 
CALCULATE(

    [Invoice Line Total],SAMEPERIODLASTYEAR('Date'[Date]))

 

The goal is to filter the 1 yr that is added so that all the line graphs end up in May 2023 rather than May 2024.

How do I change the DAX Measure to reflect that?

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    After doing some extensive research, this is my answer

    SPLY Sales Amount = 
IF(MONTH(TODAY()) >= SELECTEDVALUE('Date'[Month Num]) && YEAR(TODAY()) = SELECTEDVALUE('Date'[Year]),
CALCULATE(Sum('Sales'[Price]),SAMEPERIODLASTYEAR('Date'[Date])
),Blank())

7 Replies

  • krishb1414's avatar
    krishb1414
    Icon for Helper III rankHelper III
    3 years ago

    Hi Anonymous  You can try this measure

    SPLY INVoiced = 
VAR MaxDate = Month(Today())
VAR MinDate = Month(Today()) - 12
RETURN
CALCULATE(
    [Invoice Line Total],
    Filter(Date, 
           'Date'[Date] >= MinDate && 'Date'[Date] >= MaxDate
))


    If it solves your query, Please accept it as a solution. so that someone will get help from this.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      thanks krishb1414  , I got the answer

       

  • darkinvader_'s avatar
    darkinvader_
    Icon for Resolver I rankResolver I
    3 years ago

    Hi Anonymous , You can use the below formula :-

    Last     Year Invoice Line Total =
    VAR MaxDate     = MAX('Date'[Date])
    VAR LastYearEndDate     = EOMONTH(MAX('Date'[Date]), -12)
    VAR LastYearStartDate     = EOMONTH(LastYearEndDate, -11)
    RETURN
    CALCULATE( [Invoice Line Total],     FILTER( ALL('Date'), 'Date'[Date] >= LastYearStartDate && 'Date'[Date] <= LastYearEndDate ) )

    Please do gives a thumbs up if you find it helpfull

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      it says there is an error.

      • darkinvader_'s avatar
        darkinvader_
        Icon for Resolver I rankResolver I
        3 years ago

        Hi Anonymous ,
        Could you please send a Screenshot or description of the error

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    After doing some extensive research, this is my answer

    SPLY Sales Amount = 
IF(MONTH(TODAY()) >= SELECTEDVALUE('Date'[Month Num]) && YEAR(TODAY()) = SELECTEDVALUE('Date'[Year]),
CALCULATE(Sum('Sales'[Price]),SAMEPERIODLASTYEAR('Date'[Date])
),Blank())