Matrix with actuals and delta's versus other months

[Nipius]

I have one table with actual and historical data for our employees. Each employee has one row for each month they work in our company. The two columns I like to use in my data table are the date (which is formatted as a data type with dd MMMM yyyy) and the FTE (formatted as a decimal number).

My desired output is a matrix visualization with the total FTE sum for the newest month (in this case 30 September 2019) and then the comparison (in absolutes and percentages) versus the previous month (in this case , versus the last month of the previous year (in this case 31 December 2018) and versus the same month last year (in this case 30 September 2018).

When I update the data set, the values for the newest month will change to 31 October 2019, for previous month will change to 30 September 2019 and for the same month last year will change to 31 October 2018.

I have been breaking my head around this for some days, but I guess I'm still too much in Excel mode in my thinking. Any help would therefore be much appreciated 🙂

[Anonymous · Accepted solution]

Hi @Nipius  - Please try the following:

1. Create a Date table 
   1. Attached is a sample script that you can copy in a Blank Power Query - you may need to add a column to match the format of your date.
   2. Right-Click on the table and "Mark as Date Table".
2. Create a Relationship between the date table and your table.
3. Create Measures for Previous Month and Previous Year values.
   1. Note: These will begin by assessing what month is in context and return the value for the month prior / year prior.

      Previous Month Value = 
      CALCULATE(
          SUM(YourTable[YourColumn]), 
          PREVIOUSMONTH('Date'[Date])
      )​

      Previous Year Value = 
      CALCULATE(
          SUM(YourTable[YourColumn]), 
          SAMEPERIODLASTYEAR('Date'[Date])
      )

4. Create Measure for comparing values.
   1. Note: use the DIVIDE function instead of "/".
5. Add a Slicer Visual or Filter on Relative Month Description = "Current Month".

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[Nipius]

Thanks a ton, @Anonymous ! Could it be that you forgot to attached the sample script? 🙂

[Anonymous]

@Nipius  - I would never! Must have been a system error. 😉

Please see attached.

[Nipius]

Thanks so much, @Anonymous! Really helpful 🙂

And apologies for the delayed response. I had to go offline for some time, hence this radio silence. Again, really appreciate your support!

[sturlaws]

Hi @Nipius ,

here are some tips for getting quick replies in the forum:
How to Get Your Question Answered Quickly

Cheers,
Sturla