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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors