Forum Discussion
Aggregation and dynamic filter
- 9 years ago
Hellojs2246
So, CahabaData pretty much laid out the whole strategy for you. I went ahead and put the pbix together for you and since you are new to Power BI jumping into Power Query along with DAX might be a little overwhelming, so I created the solution only in DAX. (As Lincoln said one war at a time) . I just simply used UNION() function to append your 2016 and 2017 tables and then used some simple DAX CALCULATE()s to apply correct filters to your metrics.
Here is the download link:
https://1drv.ms/u/s!AsgNvkRwqGC7gwZj-6GQoXmWYFRWTable = UNION( '2016', '2017' ) Clients 2016 = CALCULATE( SUM('Table'[ Clients]), YEAR('Table'[Date]) = 2016 ) Clients 2017 = CALCULATE( SUM('Table'[ Clients]), YEAR('Table'[Date]) = 2017 ) 16-17 Client Growth = DIVIDE( [Clients 2017], [Clients 2016] ) -1 Visits 2016 = CALCULATE( SUM('Table'[ Visits]), YEAR('Table'[Date]) = 2016 ) Visits 2017 = CALCULATE( SUM('Table'[ Visits]), YEAR('Table'[Date]) = 2017 ) 16-17 Visit Growth = DIVIDE([Visits 2017], [Visits 2016], 0) -1
Hellojs2246
So, CahabaData pretty much laid out the whole strategy for you. I went ahead and put the pbix together for you and since you are new to Power BI jumping into Power Query along with DAX might be a little overwhelming, so I created the solution only in DAX. (As Lincoln said one war at a time) . I just simply used UNION() function to append your 2016 and 2017 tables and then used some simple DAX CALCULATE()s to apply correct filters to your metrics.
Here is the download link:
https://1drv.ms/u/s!AsgNvkRwqGC7gwZj-6GQoXmWYFRW
Table = UNION( '2016', '2017' )
Clients 2016 =
CALCULATE(
SUM('Table'[ Clients]),
YEAR('Table'[Date]) = 2016
)
Clients 2017 =
CALCULATE(
SUM('Table'[ Clients]),
YEAR('Table'[Date]) = 2017
)
16-17 Client Growth = DIVIDE( [Clients 2017], [Clients 2016] ) -1
Visits 2016 =
CALCULATE(
SUM('Table'[ Visits]),
YEAR('Table'[Date]) = 2016
)
Visits 2017 =
CALCULATE(
SUM('Table'[ Visits]),
YEAR('Table'[Date]) = 2017
)
16-17 Visit Growth = DIVIDE([Visits 2017], [Visits 2016], 0) -1