Matrix report

[Anonymous]

I would need to display the data in the below Matrix format,the final data should be like as below. Please suggest the best visualization of this and changing the headers based dates.

Formulas 

2018	Cases	Previous Year Cases formula (calculate (cases, filter(year = Previous year)
2018	Sales	Previous Year Sales formula ( same as above)
2019	Cases	Current Year cases formula
2019	Sales	Current Year sales formula

appreciate if any thoughts around this

				2018 ( this year should be changed based on Field value )	2019 ( year should dynamically change based on field)
Customer Name	Address	ZipCode	Distributor	Cases(Month Wise from Jan to dec)	Sales(Month wise from Jan to Dec)	Cases(Month Wise from Jan to dec)	Sales(Month wise from Jan to Dec)

[amitchandak]

Not Clear but you should be able to do it using year behind measure or dates ytd total ytd

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))

 and date table

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/

[Anonymous]

  

[v-xuding-msft]

Hi @Anonymous ,

As your image shown, I think the matrix visual should work. You can lay them out like below.

Matrix

Slicer

And you could click the slicer to change the header of year in Matrix. I'm not clear what you mean that change it dynamically based on field.

You could download my sample to check if it is what you want. If it is not, please share more details to us. We will understand clearly.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from the thread.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This is not resolved. I wanted to display data in Matrix report. Let me make myself clear on this.

When you select Year /Month . The data displaying Month wise and Year Totals at the end of each row.

But,I wanted to show the 2018 Months and  2019 Months ,and then 2018 Year totals/2019 Year totals side by side at the end.

also, wanted to show Grand totals at the top instead of  bottom.

[BA_Pete]

Hi @Anonymous ,

Not sure what you mean by "month wise" in your expected output example. Do you mean you want January on row 1, then Feb on row 2 etc.?