Forum Discussion

EuanKonnektis's avatar
EuanKonnektis
Frequent Visitor
6 years ago

Difference Measure between filtered column values in a matrix

Hi,   There could be a suggestion for this elsewhere, but I've been searching for a while and not found anything! I pull an export from a system every week, the spreadsheet is named the date of the...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

So, I would expect that would be something along the lines of:

 

Measure 1 = CALCULATE([Measure],PREVIOUSMONTH('Dates'[Date]))

 

Or you could brute force it similar to:

 

Measure 1 =
  VAR __Date = MAX('Table'[Date])
  VAR __Year = YEAR(__Date)
  VAR __Month = MONTH(__Date)
  VAR __Day = DAY(__Date)
  VAR __PreviousDate = 
    IF(__Month = 1,DATE(__Year - 1,12,__Day),DATE(__Year,__Month-1,__DAy))
RETURN
  CALCULATE([Measure],'Table'[Date] = __PreviousDate)

 

EuanKonnektis's avatar
EuanKonnektis
Frequent Visitor
6 years ago

Hi Greg,

 

Thanks for your quick response. Have I understood you correctly by using this measure for the first example:

Measure = CALCULATE('Data Usage'[Total Usage],PREVIOUSMONTH('Data Usage'[Export Date].[Date]))
 
That one didn't display any values in the matrix:
 

 

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    6 years ago

    Hi , EuanKonnektis 

    There's nothing wrong with Greg_Deckler  solution.

    I suspect you didn't use the correct calendar table.
    "Previousmonth" is a time intelligence function that needs to be used with the calendar table of consecutive date columns.

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • EuanKonnektis's avatar
      EuanKonnektis
      Frequent Visitor
      6 years ago

      Hi v-easonf-msft - ahhh, fantastic, I see! My export date only has a date every week.

       

      So do I need to have a consecutive date calendar date table that has a relationship with the export date column?

       

      Many thanks,

       

      Euan.

    • EuanKonnektis's avatar
      EuanKonnektis
      Frequent Visitor
      6 years ago

      I do still seem to be having issues with this and "Difference" is currently returning a blank field.

       

      I have another table called 'App Usage' which has a date row that runs every day of the month (between 02-06-2019 to today).

       

      I have set up a relationship between 'Data Usage'[Export Date] and 'App Usage'[Date].

       

      Is there still something I'm missing? Apologies, I'm still getting used to this!

       

      Here is how it looks now: