Forum Discussion

Andwu's avatar
Andwu
Icon for Helper I rankHelper 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 vis...
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

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