Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
PeteDTG
Frequent Visitor

Dax Query Month Year filter issue in Report Builder

Hello,
 

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. 

 
 

DimDate.png

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!!

 

DateTime.png

 

0 REPLIES 0

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.