Need Help to show values dynamically in column header in matrix

[sbm]

Hi Experts,

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 

Below is the example screenshots. I have selected manually Jan, Nov and Dec in screenshot.

Expected output

[Anonymous]

Hi @sbm ,

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.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[amitchandak]

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

[sbm]

Hi @amitchandak , 

Yes, I have a date table. 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  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  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.

. 

[amitchandak]

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 <=_max and 'Date'[Date >=_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-tr...

[sbm]

Hi @amitchandak ,

There is no sensitive information in this report. I have just used 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- WeekofMonthName= Text.Combine({"Week of ", [ShortMonthName], " ", Text.From([Week of Month], "en-US")})

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.

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.

Hope this is understandable now.