Forum Discussion

Daryn-TP's avatar
Daryn-TP
Regular Visitor
4 years ago
Solved

How to make a calculated column that calculates the difference between report dates

So I have data similar to the following example. I need to create a matrix like the second table example. My issue is I can't get a calculated column that calculates the % change between report dates...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Daryn-TP , Try a measure like

     

    Change % =
    var _date = maxx(filter(allselected(Table), [Location] = max(Table[Location]) && Table[Date] < Max(Table[Date])), [Date]) //Use all('Table') if one date is selected
    return
    Sum(Table[Memory Size]) - calculate(Sum(Table[Memory Size]),filter(allselected(Table) , [Location] = max(Table[Location]) && Table[Date] = _date))

     

    use all in place allselected if needed

     

Daryn-TP's avatar
Daryn-TP
Regular Visitor
4 years ago

So thankyou for the response amitchandak 

 

I tried the suggested DAX and I get an error message, "The function SUM cannot work with values of type String"

 

The dax I used is below. So in my actual datset the table is called "Virt-Data", DC=location, Meeting Date = Date. I substituted these terms into your suggested DAX. 

Change % =
var _date = maxx(filter(allselected('Virt-Data'), [DC] = max('Virt-Data'[DC]) && 'Virt-Data'[Meeting Date] < Max('Virt-Data'[Meeting Date])), 'Virt-Data'[Meeting Date]) //Use all('Table') if one date is selected
return
Sum('Virt-Data'[Memory Utilization (%)]) - calculate(Sum('Virt-Data'[Memory Utilization (%)]),filter(allselected('Virt-Data') , 'Virt-Data'[DC] = max('Virt-Data'[DC]) && 'Virt-Data'[Meeting Date] = _date))
 
Not sure if I messed up the syntax when substituting terms though. 
Daryn-TP's avatar
Daryn-TP
Regular Visitor
4 years ago

Nevermind I figured it out I had one of my parameters set as an alphanumeric rather than a number. Thank you!