Forum Discussion

sbm's avatar
sbm
Icon for Helper II rankHelper II
6 years ago

Need Help to show values dynamically in column header in matrix

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 hea...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
sbm
Icon for Helper II rankHelper II
6 years ago

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's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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-trend/ba-p/882970

     

    • sbm's avatar
      sbm
      Icon for Helper II rankHelper II
      6 years ago

      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.