How to use this table to do desired visualizations and computations?

[Anonymous]

Hi!

Doing some transformations, I got the following table in the Power Query Editor:

Now, I am asked to create visualizations that include all measures in the data over time and to do  Year-over-year calculations for all the measures.

I am not sure how to do both tasks.

For example, how could I use the table to do the year-over-year calculations for all the measures? I tend to use the DAX-Funtion SAMEPERIODLASTYEAR by creating a Measure, but don't know how to do that here (cannot use SUM or something like that over the Value column...).

[v-yadongf-msft · Accepted solution]

Hi @Anonymous ,

Please add an index column in Power Query:

Please try following DAX:

YOY = 
var cur_wert = 'Table'[Wert]
var pre_wert = CALCULATE(
    MAX('Table'[Wert]),FILTER('Table','Table'[Index] = EARLIER('Table'[Index]) -1 && 'Table'[Measure] = EARLIER('Table'[Measure]))
    )
return
DIVIDE(cur_wert - pre_wert,pre_wert)

Year-over-year calculations for all the measures:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VijayP]

@Anonymous 

selected year - calculate(sum(wert),measure="Revenue")
you must have date table

Previous year - calculate(selected year,sameperiodlastyear(datedim[date))

like wise create all measures

[Anonymous]

Hi! @VijayP 

i am not sure that I do understand you correctly.

You suppose to create a measure 

selected year = calculate(sum(wert),measure="Revenue")

but this sums the entries of the column wert for all years and for Revenues...

Is this what it is supposed to be?