Forum Discussion

powerbi56's avatar
powerbi56
Frequent Visitor
4 years ago
Solved

Slicer

Hello, I am trying to add a slicer which compares the numbers from this year to the numbers from last years in a comparision visualation showing the past 12 months. 

 

I have the DAX written for the slicer to show the numbers for the slicer for the last 12 months  - I have disconnected the date table and have this-

2 =
VAR CurrentDate = MAX('Date'[Month])
VAR PreviousDate = DATE(YEAR(CurrentDate),Month(CurrentDate)-12,DAY(CurrentDate))
VAR RESULT=
CALCULATE(
('DATA' Total]),
FILTER(
'DATA',
'DATA'[Month] >= PreviousDate && 'DATA'[Month] <= CurrentDate
)
)
RETURN
RESULT 
 
 
 
But it wont let me filter it when I add SAMEPERIODLASTYEAR
CALCULATE(
('DATA' Total]), SAMEPERIODLASTYEAR('DATA'[Month]))
So it shows all the data from all previous months. 
 
Can someone help. or have a different method. Thank you! 
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi powerbi56 ,

    According to your description, I create a sample.

    By your formula, get the same result as you described.

    Here's my solution, modify the formula like this:

    Last 12 before 12 =
IF (
    [Last 12] = BLANK (),
    BLANK (),
    CALCULATE ( 'Data'[Total], SAMEPERIODLASTYEAR ( Data[Month] ) )
)

    Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    powerbi56 , Refer

     

    //Date1 is independent Date table, Date is joined with Table
    Last 12  =
    var _max = maxx(allselected(Date1),Date1[Date])
    var _min = eomonth(_max, -12) +1
    return
    calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

     

     

    //Date1 is independent Date table, Date is joined with Table
    Last 12 before 12 =
    var _max = eomonth(maxx(allselected(Date1),Date1[Date]),-12)
    var _min = eomonth(_max, -12) +1
    return
    calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

     

    or

     

    Last 12 before 12 = calculate([Last 12], dateadd(date[date],-1, year))

    • powerbi56's avatar
      powerbi56
      Frequent Visitor
      4 years ago

      Thank you so much! It worked, but they are on two different areas, I wonder if there is a way to combine them. For example it looks like this right now. 

       

      Is there a way to have them combined like this? 

       

       

      Thank you!! 

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi powerbi56 ,

    According to your description, I create a sample.

    By your formula, get the same result as you described.

    Here's my solution, modify the formula like this:

    Last 12 before 12 =
IF (
    [Last 12] = BLANK (),
    BLANK (),
    CALCULATE ( 'Data'[Total], SAMEPERIODLASTYEAR ( Data[Month] ) )
)

    Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.