Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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.
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))
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.
.
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...
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.