cancel
Showing results for 
Search instead for 
Did you mean: 
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 Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!