Need Help to show values dynamically in column header in matrix

Question

Hi Experts,&nbsp;I have a below a report based on a direct query storage mode with year and month filter. I want to create it dynamic to show column header. if I select Jan month in filter, column header show the last 2-month for Nov and Dec including Jan to see the trends and when I select Feb, column header should show with Nov and Dec and so on&nbsp;Below is the example screenshots. I have selected manually Jan, Nov and Dec in screenshot.&nbsp;&nbsp;&nbsp;&nbsp;Expected output

amitchandak · Answer

I am assuming you have a calendar table. Try
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-3,MONTH))

&nbsp;

amitchandak · Answer

That is what rolling should have done. You can also try this
Rolling 3 = 
varr _max = maxx('Date', 'Date'[Date])
var _min  = minx('Date',dateadd('Date'[Date],-3,MONTH))
return
CALCULATE(sum(Sales[Sales Amount]),filter(all(date), 'Date'[Date &lt;=_max and 'Date'[Date &gt;=_min)
If it does not solve.
Can you share sample data and sample output. If possible please share a sample pbix file after removing sensitive information.Thanks. Proud to be a Datanaut My Recent Blog -https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
&nbsp;

anonymous · Answer

Hi&nbsp;sbm&nbsp;,&nbsp;Check the topic below and see if it helps.https://community.powerbi.com/t5/Desktop/How-to-hide-null-columns-in-matrix-table/m-p/562296#M265307.&nbsp;Best Regards,JayCommunity Support Team _ Jay WangIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

sbm · Answer

Hi&nbsp;amitchandak&nbsp;,&nbsp;Yes, I have a date table.&nbsp;The following formula returns the sales for the 3 months prior to the selected date. but I have not a requirement of previous 3-month sales. I have a measure of total sales. for now, I am selecting manually for Jan, Nov and Dec month to show total sales but&nbsp; I want to show dynamically if I select any single month from month filter, for example, Jan month then total sales should show also for&nbsp; Nov and Dec month with Jan as well in matrix visual. if I select Feb month then total sales should show Feb , Nov and Dec like this.&nbsp;&nbsp;&nbsp;&nbsp;.&nbsp;&nbsp;

sbm · Answer

Hi&nbsp;amitchandak&nbsp;,There is no sensitive information in this report. I have just used&nbsp;AdventureWorks data sample in power bi based on Calander, Sales and Product table. I have created a week of month column and then one more based on week of month column in power query.Here is formula-&nbsp;WeekofMonthName=&nbsp;Text.Combine({"Week of ", [ShortMonthName], " ", Text.From([Week of Month], "en-US")})&nbsp;Please see the all fields I have used in the attached image. I can share the PBIX. file but I can not see the option to upload here.&nbsp;I think you didn't understand my question. I do not need to calculate rolling sales. I just want to show total sales value in this report dynamically if I select the month of Jan from month filter then it should also populate with Nov and Dec total sales alongside Jan.Currently I have selected manullly Mar, Nov, and Dec.So my aim is that If I select a single month then It should filter last 2-month in matrix visual alongside to compare with selected month.&nbsp;Hope this is understandable now.&nbsp;&nbsp;&nbsp;

Forum Discussion

Need Help to show values dynamically in column header in matrix

5 Replies

Related content

how to dynamically replace values for multiple columns

Dynamic Column headers

table with dynamic values (columns)

Dynamic Column Name

Splitting Column values