How to use Summarizecolumns to filter by Year and Description + sum Total Revenue

[fcarvalho75]

Hi,

I'm brand new on PowerBi, I have 3 facts and I need to get the % of comparison to the previous year.

Basically, there are 4 fields:

1) Year

2) Month

3) Type

4) Revenue

I need to make a formula in order to get the % of year and % by month (same period).

I tried many things, but I am not able to solve it yet.

Any help, I really appreciate it.

Fernanda

[Phil_Seamark]

Can you please post a sample of your data.

[fcarvalho75]

I was able to create a new table using:

Total2017 = SUMMARIZECOLUMNS(dm_calendar[skCalendar];dm_calendar[year];dm_calendar[month];ft_operational[typeDescription];"Total Revenue per Year";sum(ft_operational[amount])/1000)

However I need to filter by year, for example: Year = 2016

Also, I tried to create a measure, that works, but I need to make the same by Month and I get error.

(RevenueCurrentYear) =
VAR
dataref=[(dataref)]
return

CALCULATE([(Total Revenue)];'dm_calendar'[year] = year(dataref))

Tks a lot

[v-sihou-msft · Accepted solution]

@fcarvalho75

In DAX, you can add FILTER() in SUMMARIZACOLUMNS() function, Like:

Total =
SUMMARIZECOLUMNS (
    dm_calendar[skCalendar],
    dm_calendar[year],
    dm_calendar[month],
    ft_operational[typeDescription],
    FILTER ( dm_calendar, dm_calendar[year] = 2016 ),
    "Total Revenue per Year", SUM ( ft_operational[amount] ) / 1000
)

If you need to filter by Month, you need to filter on both Year and Month.

(RevenueCurrentMonth) =
VAR
dataref=[(dataref)]
return

CALCULATE([(Total Revenue)],

FILTER('dm_calendar',

'dm_calendar'[year] = year(dataref) &&

'dm_calendar'[month] = month(dataref)

)

)

Regards,

[fcarvalho75]

Simon, thank you so much! This is exactly what I was looking for! Really appreciate it.

[Phil_Seamark]

Hi there,

I recommend you add a column to your fact table that is using the DATE datatype, so perhaps add a calcuated column like this

So perhaps in the Query Editor or keep from  your source system have a column that you can add the following forumlas over:

There are a bunch of handy functions in DAX that allow you do to period comparisons but they need to work on a column that is a datetime rather than an integer.

Sum of Revenue LY = 
        CALCULATE(
                  SUM(Table1[Revenue]),
                  DATEADD('Table1'[myDateColumn],-12,MONTH)
		    	)

Percentage Change = DIVIDE([Sum of Revenue], ([Sum of Revenue]-[Sum of Revenue LY]))