year over year comparision with SSAS data source

[Sébastien_S]

Hello all,

Currently working on Power BI Desktop, i have a report with monthly data starting from january 2019.
I would like to make a graph allowing to compare months from 2019 and 2020.

My data come from an SQL Server Analysis service, so I can't build a column with only the month name without year number.

It's not "my" cube, so I can't neither modify it to add columns as I want.

How could I do this with Power BI ?
Maybe with a custom visual ?

Thanks a lot for your help !

[amitchandak]

@Sébastien_S 

You can have measures like this :

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

In case you have a date.

[Sébastien_S]

Hello @amitchandak ,

Thanks for the quick answer.

Unfortunately, i'm not able to create new measures since my data set is an SSAS cube, with direct connection.

Or I don't know how to do this.

[TomMartens · Accepted solution]

Hey @Sébastien_S ,

it seems you have a live connection to SSAS Multidimensional.

This means you can't achieve what you are looking for, as SSAS Multidimensional live connections do not allow creating report based measures.

Without altering the SSAS measures you are stuck.

Regards,

Tom