I need Measure data format

[Aruna_ln]

HI anybody help me

MY
3 reports i am using SSAS is source
1st report scenario :i am using Matrix
Row is Name
Column is Year
value is amount

anybody help Column shoulbe Two columns one is CurrentYear 2nd column is previous year (mean comarison current year vs previous)
exp:2017,2016 if next year automatically refresh my columns 2018 and 2017 like
How to create Measure ?

2 Report scenario: here row shoulbe last 12 month:i need last 12 month list ( mean 2017-11 this month i need last 12 like 2017-10,2017-09,2017-08,..2016-11)
automatically i need updates next month mean like 2017-12 to 2016-12) like
how to create Measure?
3 Report scenario.current month year last year same month( mean 2017-11 this year,previous year 2016-11)

[Abduvali]

Hi @Aruna_ln,

I'm not sure if the following will work with analysis services but have a look:

https://www.pbiusergroup.com/viewdocument/current-last-year

Regards

Abduvali

[parry2k]

@Abduvali how are you connecting to SSAS? Is it live query or import?

[Aruna_ln]

Hi parry,
I am connecting live query.

[v-yuezhe-msft]

@Aruna_ln,

For your first question, in "Connect Live" mode, when you put year in Column of Matrix visual, it is not possible to use measure in Power BI Desktop to limit the column to show current year and previous year. I would recommend you limit year to current year and last year using MDX or DAX query at the beginning that you connect to SSAS database.

About your second question, do you want to show amount of last 12 month? If so, you can create measure as described in this similar thread.

About the third question, create the following measures.

Amount of Current Year Month = CALCULATE(SUM('Table'[Amount]), FILTER('Table',YEAR('Table'[Date])=YEAR(TODAY())&&MONTH('Table'[Date])=MONTH(TODAY())))

Amount of last Year Month = CALCULATE(SUM('Table'[Amount]), FILTER('Table',YEAR('Table'[Date])=YEAR(TODAY())-1&&MONTH('Table'[Date])=MONTH(TODAY())))

If above measures don't help, please share sample data and post expected result in table format.


Regards,
Lydia