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.