Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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,
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
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,
Simon, thank you so much! This is exactly what I was looking for! Really appreciate it.
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]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |