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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors