Need to display sales and purchase data in below format using matrix. Please help

[Anonymous]

Need to display sales and purchase data in below format using matrix. Please help.

[Anonymous]

Hi @Anonymous ,

Any updates?

Best Regards,
Eyelyn Qin

[Anonymous]

Hi @Anonymous ,

I saw a date slicer in your report, so if you want to dynamically change the DAY/WTD/MTD/YTD based on the selected date?

For example, if select 2021/10/8 in slicer, then 

DAY:2021/10/8

WTD: 2021/10/4-2021/10/8

...

if select 2021/10/13 in slicer, then 

DAY:2021/10/13

WTD: 2021/10/11-2021/10/13

...

Best Regards,
Eyelyn Qin

[Anonymous]

Hi @Anonymous ,

Not very clear about your requirement. 

But in your case, I think creating a new table for matrix's row is an effective way.

Best Regards,
Eyelyn Qin

[Anonymous]

Hi @Anonymous ,

I have used the following formula to built a data table:

Table = ADDCOLUMNS( CALENDAR(DATE(2020,10,1),DATE(2021,12,31)),"Sales",1,"Purchase",2, "Year-Week",YEAR([Date]) *100+WEEKNUM([Date],2))

According to your expected output, {"DAY","WTD","MTD","YTD"} is acted as Row in Matrix visual . 

So I'd suggest you create a new table with a column which contains these values as shown below:

For Matrix = {"DAY","WTD","MTD","YTD"}

Then you could use your seperated measures for Sales and Purchase:

Sales = SWITCH(MAX('For Matrix'[Value]),
"DAY",CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Date]=TODAY())),
"WTD",CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Date]<=TODAY() && [Year-Week]=YEAR(TODAY())*100+WEEKNUM(TODAY(),2))),
"MTD",CALCULATE(SUM('Table'[Sales]),DATESMTD('Table'[Date])),
"YTD",CALCULATE(SUM('Table'[Sales]),DATESYTD('Table'[Date])))

 Below is the final output:

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

[Anonymous]

Hi  @Anonymous , thanks a lot for the help. Can we use related function for passing sales and purchase data in data table? In the above example you are passing some static values, right?

[Anonymous]

hI @Anonymous , Can you please check below sample pbix file. 

https://drive.google.com/file/d/1RKcpJAzfVlVFucnt48tPb3yURC-LtBwu/view?usp=sharing 

[amitchandak]

@Anonymous,  this seems like split measure into measure and dimension

examples

union(
summarize('Table',"Measure","sales","This period",[SALES YTD],"Last period",[SALES LYTD],"POP",[SALES YOY])
summarize('Table',"Measure","unit","This period",[unit YTD],"Last period",[unit LYTD],"POP",[unit YOY])
)

or

union(
summarize('Table',"Measure parent","Measure1","Measure",[Measure1])
summarize('Table',"Measure parent","Measure2","Measure",[Meausre2])
)

or

union(
summarize('Table',"Measure","App","Last 7 Days",[APP 7 Day],"Last 14 days",[APP 14 days],"last 21 Days",[APP 21 Days])
summarize('Table',"Measure","App","Last 7 Days",[Leads 7 Day],"Last 14 days",[Leads 14 days],"last 21 Days",[Leads 21 Days])
)

or

union(
summarize('Table','Table'[country],"Revenue","sales","This period",[Revenue YTD],"Last period",[Revenue LYTD],"POP",[Revenue YOY])
summarize('Table','Table'[country],"Costs","unit","This period",[Costs YTD],"Last period",[Costs LYTD],"POP",[Costs YOY])
)

You need to add group bys what you need as filter. This new table will not take old slicer filter

[Anonymous]

I have created seperated measures for sales and purchase. AlosI want to show split of these measures in a matrix. Any idea?