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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Andwu
Helper I
Helper I

Apply Filter on Date Aggregation (3M, 6M, MAT)

I have connected my Power BI Desktop to an SSAS Server (2014). The data model has a date aggregation dimension with the following values (MAT, 3M, 6M, None, YTD). I'd like to create the following visual:

 

* Rows: Product Category

* Columns: Date Aggregation (3M, 6M, MAT, YTD (basically everything except of None))

* Values: Sales

 

Additionally I have added a filter on Month (selected on Aug 15).

 

The visual is shown as expected, but I'm not able to get rid of none. If I apply a filter on the Date Aggregation table (as visual level filter or page level filter ), I can just select one specific date aggregation, but not select several or unselect none.

 

Also if I add a slicer on Date Aggregation, the slicer selection controls are not shown.

 

Any ideas why this is not working and what I can do to achieve this. As mentioned, I have set a filter on a specific month, so that shouldn't be the reason.

 

Thanks,

 

André

1 ACCEPTED SOLUTION
Andwu
Helper I
Helper I

Unfortunately I do not know the specification of the cube. However I installed the latest version of Power BI Desktop and now I can set the filter as planned by marking the appropiate Date aggregations by holding CTRL. So my primary request has been solved.

 

Thanks

View solution in original post

4 REPLIES 4
ymulyana
Frequent Visitor

Hi there,

 

I have make formula for

 

MAT

 

MAT=if(COUNTROWS(DATESINPERIOD(DimDate[Date],LASTDATE(DimDate[Date]),-12,MONTH))=12,CALCULATE(sum('Page1'[sales]),DATESINPERIOD(DimDate[Date],LASTDATE(DimDate[Date]),-12,MONTH),all(DimDate)),0)

 

if you would like to 3M, 6M just change -12 to the period you want.

 

YTD

 

YTD=CALCULATE(sum('Page1;[SALES]),DATESYTD(DimDate[Date]),all(DimDate))

 

Hope this answer work..

 

 

ymulyana
Frequent Visitor

Hi there,

 

I have make formula for

 

MAT

 

MAT=if(COUNTROWS(DATESINPERIOD(DimDate[Date],LASTDATE(DimDate[Date]),-12,MONTH))=12,CALCULATE(sum('Page1'[sales]),DATESINPERIOD(DimDate[Date],LASTDATE(DimDate[Date]),-12,MONTH),all(DimDate)),0)

 

if you would like to 3M, 6M just change -12 to the period you want.

 

YTD

 

YTD=CALCULATE(sum('Page1;[SALES]),DATESYTD(DimDate[Date]),all(DimDate))

 

Hope this answer work..

 

 

Andwu
Helper I
Helper I

Unfortunately I do not know the specification of the cube. However I installed the latest version of Power BI Desktop and now I can set the filter as planned by marking the appropiate Date aggregations by holding CTRL. So my primary request has been solved.

 

Thanks

v-qiuyu-msft
Community Support
Community Support

Hi @Andwu,

 

How did you define the date aggregation dimension and values (MAT, 3M, 6M, None, YTD)? Please share some screenshots about the report if possible.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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