Forum Discussion

Andwu's avatar
Andwu
Helper I
9 years ago
Solved

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é

Need Help
  • Andwu's avatar
    Andwu
    9 years ago

    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

4 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    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

  • Andwu's avatar
    Andwu
    Helper I
    9 years ago

    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

  • ymulyana's avatar
    ymulyana
    Frequent Visitor
    9 years ago

    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's avatar
    ymulyana
    Frequent Visitor
    9 years ago

    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..