Update Month year dynamically in Table or matrix visual .

[PunitPrabhu]

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 plotting it in Table visual or Matrix visual the months and other value columns are taken indivdually . I need a solution where the month change dynamically in the visual .

In attached image the Month year range is June 23 to June 22 . Now in the month of July 23 when data is refreshed July 23 needs to be added mannually again in visual and June 22 gets deleted in sql query due to which i get error in power bi . The new range should be July 23 to July 22 along with other columns.

Sample Data 

Please guide me how to resolve this. Thanks

[amitchandak]

@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]

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.

[v-rzhou-msft]

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]

Hi @v-rzhou-msft 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