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

[Andwu]

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é

[ymulyana]

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]

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 · Accepted solution]

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]

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