cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nipius Regular Visitor
Regular Visitor

Matrix with actuals and delta's versus other months

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 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Matrix with actuals and delta's versus other months

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.
Nathan Peterson

View solution in original post

5 REPLIES 5
sturlaws New Contributor
New Contributor

Re: Matrix with actuals and delta's versus other months

Hi @Nipius ,

 

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

 

Cheers,
Sturla

Highlighted
Super User
Super User

Re: Matrix with actuals and delta's versus other months

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.
Nathan Peterson

View solution in original post

Nipius Regular Visitor
Regular Visitor

Re: Matrix with actuals and delta's versus other months

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

Super User
Super User

Re: Matrix with actuals and delta's versus other months

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

Please see attached.

Nipius Regular Visitor
Regular Visitor

Re: Matrix with actuals and delta's versus other months

Thanks so much, @natelpeterson! 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!

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,313)