Forum Discussion

Lenastray's avatar
Lenastray
Helper I
4 years ago

Show 2 specific date variation

Hello:

Im generating a report with the variation agregation of 2 specific dates. As an atempt, I create a measure like:

 

VAR m1 = CALCULATE(SUM(.), some_specific_column_filters, DateTable[Date] = some_date)
VAR m2 = CALCULATE(SUM(.), some_specific_column_filters, DateTable[Date] = some_date2)

VAR m2 - m1

 

But it did generate a sort of union of m1 and -m2
Then, I tried to make a trick with dateadd, like 

 

VAR m1 = CALCULATE(SUM(.), some_specific_column_filters)
VAR m2 = VAR m1 = CALCULATE(SUM(.), some_specific_column_filters, DateTable[Date] = dateadd(some_specific_offset))
RETURN m1 - m2

 

And it dit it, but for all dates, so I have the variation for all dates by that specific offset
I try to addapt the previous result to get the required date variation (just to show the specific date row) like 

 

return calculate(m1-m2, DateTable[Date]=specific_date, lastdate(DateTable[Date]))

 

but it didnt make any diference.
How could I make the desired mesure? Thanks in advance

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Lenastray , 1.  min and max date of the connected date table 

     

    example

    measure =
    var _min = minx(allselected(Date), Date[Date])
    var _max = maxx(allselected(Date), Date[Date])
    return
    calculate([sales], filter(Date,Date[Date]= _min))-calculate([sales], filter(Date,Date[Date]= _max))

     

    or

     

    measure =
    var _min = minx(allselected(Date), Date[Date])
    var _max = maxx(allselected(Date), Date[Date])
    return
    calculate([sales], filter(Date,Date[Date]= _max))-calculate([sales], filter(Date,Date[Date]= _min))

     

     

    or use two period slicer approach

    How to use two Date/Period slicers

    https://youtu.be/WSeZr_-MiTg

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Lenastray ,

    You can refer the following link to get the difference:

    DIFFERENCE BETWEEN SUM OF VALUES FOR RECENT DATE AND 2ND MOST RECENT DATE

    Current sales sum =
VAR MAX_DATE =
    MAX ( ‘Table'[Date] )
RETURN
    CALCULATE ( SUM ( ‘Table'[Sales] ), ‘Table'[Date] = MAX_DATE )

2nd most recent date =
VAR current_date =
    MAX ( ‘Table'[Date] )
RETURN
    CALCULATE ( MAX ( ‘Table'[Date] ), ‘Table'[Date] < current_date )

2nd most recent date Sales =
VAR previous_date_just = [2nd most recent date]
RETURN
    CALCULATE ( SUM ( ‘Table'[Sales] ), ‘Table'[Date] = previous_date_just )

Difference =
[Current sales sum] – [2nd most recent date Sales]

    If the above one can't help you get the expected result, please provide some sample data(without sensitive info) and your expected result with backend logic and specific examples for the purpose of providing you a suitable solution. Thank you. 

    Best Regards