Forum Discussion

PunitPrabhu's avatar
PunitPrabhu
Frequent Visitor
3 years ago

Update Month year dynamically in Table or matrix visual .

I have data import from SQL from a view table . In SQL query there are 12 month year column and other value columns . As per current month the it changes previous 11 months.  In Power BI while plotti...
Need Help
amitchandak's avatar
amitchandak
Super User
3 years ago

PunitPrabhu , Create an independent date (with not join) and have column like

Month Type = Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY")
)

 

Save this slicer on This Month

 

and have measure like this using joined date table

 

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

 

Power BI Abstract Thesis: Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

PunitPrabhu's avatar
PunitPrabhu
Frequent Visitor
3 years ago

Thank you amitchandak will try it Thanks a lot . With this menthod i am not able to take other value columns in the same table which are in currency format and number format.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi PunitPrabhu ,

     

    According to your sample, I know you have the multiple date values are column headers in your table. And you want your visual to show data in dynamic date range with the changing of month.


    I think this is not a good choice to achieve your goal. Power BI doesn't support us to add columns or remove columns automaticlly.


    I suggest you to unpivot the multiple date columns which contains data. Rename the attribute column as Date column.
    Then your new table will only have Key columns, [Date] column and [Value] colume.

     

    Finally, you can create a Matrix visual to achieve your goal. Add Keycolumns into matrix Row Field, add [Date] column into matrix Column Field and add [Value] column into matrix Value Field.

     

    Best Regards,
    Rico Zhou

     

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

    • PunitPrabhu's avatar
      PunitPrabhu
      Frequent Visitor
      3 years ago

      Hi Anonymous Rico Zhou -- Thanks for your response . The solution which you mentioned i did that already. Using that method i need to create 2 separate tables one for Dynamic month and one for other value columns which have currency and number format which works fine. I was checking if its possible with dax measure it can be done in one matrix table itself