I have a report I have built in Power BI and I am copying over the Dax Query into Report Builder, which is being used as a filter for month year. I have changed the month year column to a date format in Power BI, as shown in the image below.
However, when copying over the Dax Query it changes the MonthYear column to datetime. Here is the Dax Query:
// DAX Query DEFINE VAR __DS0FilterTable = TREATAS({"FALSETRUE", "TRUEFALSE"}, 'edw amcs_prod_factTonnage'[Exclude/Include]) VAR __DS0FilterTable2 = TREATAS( {DATE(2021, 8, 1), DATE(2021, 9, 1), DATE(2021, 10, 1), DATE(2021, 11, 1), DATE(2021, 12, 1)}, 'edw dimDate'[MonthYear] ) VAR __DS0FilterTable3 = TREATAS({"2021"}, 'edw dimDate'[Year]) VAR __DS0Core = CALCULATETABLE( SUMMARIZE('edw dimDate', 'edw dimDate'[MonthYear], 'edw dimDate'[Month]), KEEPFILTERS(__DS0FilterTable), KEEPFILTERS(__DS0FilterTable2), KEEPFILTERS(__DS0FilterTable3) ) VAR __DS0PrimaryWindowed = TOPN(501, __DS0Core, 'edw dimDate'[Month], 1, 'edw dimDate'[MonthYear], 1) EVALUATE __DS0PrimaryWindowed ORDER BY 'edw dimDate'[Month], 'edw dimDate'[MonthYear]
This is causing my Report Builder filter to show month year as a datetime, as shown below. It works fine, but looks weird. How to I go about fixing this, so it actually shows month year (September 2020)?
Can I do this in the Dax Query or does it have to happen in Report Builder? Haven't had much success with either yet.
Thank you!!