Forum Discussion
Prior year Matrix column
Hi, I have a matrix table in my report that I want to show total sales by employee, per month and have a total sales for the previous year.
I have a SQL
dimension called DimEmployee, (EmpId(PK), EmpName),
UK format...
dimenstion called DimDate (DateKey(PK), FullDate(DD/MM/YYY/), FiscalYear, FiscalMonth, FiscalMonthName) -- Fiscal Month is an int, (1 = April etc... used to order FidcalMonthName) -- FiscalMonthName is a varchar, April, may, June, etc...
Fact table called FactSales(EmpId(FK), SaleAmount deciaml, DateKey(FK))
So I have a slicer that oulls its value from DimDate(FiscalYear) to filter the matrix to one fiscal year,
The Matrix visual is configured as
Rows - DimEmployee.EmpName
Columns - DimDate.FiscalMonthName
Values - FactSales.SaleAmount -(Summed from the drop down)
The table looks something like...
| Employee | April | May | .... etc | Total (Calculated by the matrix) |
| Emp1 | 10 | 20 | 30 | 60 |
| Emp2 | 5 | 10 | 10 | 25 |
However, we want to have a Prior Year total column at the start, so someting like....
| Employee | Prior Year Total | April | May | .... etc | Total(YDT) |
| Emp 1 | 500 | 20 | 20 | 10 | 50 |
I can't find out, how to achive this though.
I have a Metrix table with 2 measures
Current year = SUM(FactSales[SalesAmount]) - filtered by the report slicer of fiscal year
Prior Year = CALCUALTE([CurrentYear], SAMEPERIODLASTYEAR(DimDate[FullDate]))
The values work fine in a bar chart, but I can find a way to implement it into the matrix in the format above
Anyone faced this issue?
4 Replies
First, sample data and measure formulas are of great assistance. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.
Also, See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008Not clear,
In case you have a date table and need YTD or prior YTD, you can use datesytd and totalytd . In both these, you can give year end date
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31")) This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31")) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year)) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(dateadd('Date'[Date],-1,Year),-21,Day))"12/31" is year-end date, you can change it say "3/31", year end in march
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedinupdated the original post to hopefully make more sense
Hi RobbLewz
Matrix doesn't support this kind of format by default.
Would you minding adding rows to your data model or creating calculated tables?
This workaround is not dynamic.
Best Regards
Maggie
