Forum Discussion
Dynamic comparison tables using separate date filters
Hi,
I have a main facts table that includes date, costs, clicks etc.
I also have a separate dates table with a relationship setup with facts table, with elements such as date, YY_WW, YY_MM
I want to be able to generate a table with 3 columns.
Period 1 totals, Period 2 totals, % difference.
Then would have metrics as rows, such as clicks, cost
Then to use two use different date slicers so can dynamically change the sum for either period on selected value. Based on either 'date', 'YY_WW' or 'YY_MM'.
So user could decide to compare 3 weeks ago vs. 6 weeks ago. So more dynamic than using something like this week vs. last week where one or both periods is more fixed.
Possibly with use of things like 'selectedvalue', 'all', 'filter' within the measures, but not sure how to do.
Any help appreciated.
Thanks,
Chris
Hi, Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Metrics:
Calendar 1(a calculated table):
Calendar 1 = CALENDARAUTO()Calendar 2(a calculated table):
Calendar 1 = CALENDARAUTO()There is no relationship between tables. You may create two measures as below.
Period 1 totals = IF( HASONEVALUE(Metrics[Metric]), IF( SELECTEDVALUE(Metrics[Metric])="Cost", CALCULATE( SUM('Table'[Cost]), FILTER( ALL('Table'), [Date] in DISTINCT('Calendar 1'[Date]) ) ), CALCULATE( SUM('Table'[Click]), FILTER( ALL('Table'), [Date] in DISTINCT('Calendar 1'[Date]) ) ) ) )Period 2 totals = IF( HASONEVALUE(Metrics[Metric]), IF( SELECTEDVALUE(Metrics[Metric])="Cost", CALCULATE( SUM('Table'[Cost]), FILTER( ALL('Table'), [Date] in DISTINCT('Calendar 2'[Date]) ) ), CALCULATE( SUM('Table'[Click]), FILTER( ALL('Table'), [Date] in DISTINCT('Calendar 2'[Date]) ) ) ) )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi, Anonymous
If you take the answer of someone, please mark it as the solution to help the other member0s who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.
Best RegardsAllan
Anonymous , refer my blog on how to use two date table
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
But you can use time intelligence too. refer my blog on
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123Hi, Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Metrics:
Calendar 1(a calculated table):
Calendar 1 = CALENDARAUTO()Calendar 2(a calculated table):
Calendar 1 = CALENDARAUTO()There is no relationship between tables. You may create two measures as below.
Period 1 totals = IF( HASONEVALUE(Metrics[Metric]), IF( SELECTEDVALUE(Metrics[Metric])="Cost", CALCULATE( SUM('Table'[Cost]), FILTER( ALL('Table'), [Date] in DISTINCT('Calendar 1'[Date]) ) ), CALCULATE( SUM('Table'[Click]), FILTER( ALL('Table'), [Date] in DISTINCT('Calendar 1'[Date]) ) ) ) )Period 2 totals = IF( HASONEVALUE(Metrics[Metric]), IF( SELECTEDVALUE(Metrics[Metric])="Cost", CALCULATE( SUM('Table'[Cost]), FILTER( ALL('Table'), [Date] in DISTINCT('Calendar 2'[Date]) ) ), CALCULATE( SUM('Table'[Click]), FILTER( ALL('Table'), [Date] in DISTINCT('Calendar 2'[Date]) ) ) ) )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for this!
It works well, except I can't use filters on the data. I guess due to the ALL function, or because the metrics table isn't connected to the main facts table. Is there a way around this, so I could filter by account_name for example in the facts table. At moment, if I do filter for this, it just gives overall total, and is same for each account_name.
Thanks,
Chris
I kind of combined two above methods, but doesn't quite work as stuck on the first metric (impressions). Any idea what fix could be? Or if different way? Thanks for help!
P1 totals v2 =
var cur_start=min(Dates_Table2[Date])
var cur_end=max(Dates_Table2[Date])
return
IF(
HASONEVALUE(Metrics[Metrics]),
IF(SELECTEDVALUE(Metrics[Metrics])="Impressions",
CALCULATE(
SUM('Analytics_Data_Presto'[Impressions]),
Analytics_Data_Presto[Date]>=cur_start && Analytics_Data_Presto[Date]<=cur_end)
),
IF(
HASONEVALUE(Metrics[Metrics]),
If(selectedvalue(Metrics[Metrics])="Clicks",
CALCULATE(
SUM('Analytics_Data_Presto'[Clicks]),
Analytics_Data_Presto[Date]>=cur_start && Analytics_Data_Presto[Date]<=cur_end)
),
IF(
HASONEVALUE(Metrics[Metrics]),
If(selectedvalue(Metrics[Metrics])="Costs",
CALCULATE(
SUM(Analytics_Data_Presto[Costs]),
Analytics_Data_Presto[Date]>=cur_start && Analytics_Data_Presto[Date]<=cur_end)
),
IF(
HASONEVALUE(Metrics[Metrics]),
If(selectedvalue(Metrics[Metrics])="CTR",
CALCULATE(
(SUM('Analytics_Data_Presto'[Clicks])/sum(Analytics_Data_Presto[Impressions])*100),
Analytics_Data_Presto[Date]>=cur_start && Analytics_Data_Presto[Date]<=cur_end)
),
IF(
HASONEVALUE(Metrics[Metrics]),
If(selectedvalue(Metrics[Metrics])="CPC",
CALCULATE(
(SUM('Analytics_Data_Presto'[Costs])/sum(Analytics_Data_Presto[Clicks])),
Analytics_Data_Presto[Date]>=cur_start && Analytics_Data_Presto[Date]<=cur_end)
),
IF(
HASONEVALUE(Metrics[Metrics]),
If(selectedvalue(Metrics[Metrics])="Conversions",
CALCULATE(
SUM('Analytics_Data_Presto'[Conversions]),
Analytics_Data_Presto[Date]>=cur_start && Analytics_Data_Presto[Date]<=cur_end)
),
IF(
HASONEVALUE(Metrics[Metrics]),
If(selectedvalue(Metrics[Metrics])="Conversion Value",
CALCULATE(
SUM('Analytics_Data_Presto'[Conversion Value]),
Analytics_Data_Presto[Date]>=cur_start && Analytics_Data_Presto[Date]<=cur_end)
),
IF(
HASONEVALUE(Metrics[Metrics]),
If(selectedvalue(Metrics[Metrics])="CVR",
CALCULATE(
(SUM('Analytics_Data_Presto'[Conversions])/sum(Analytics_Data_Presto[Clicks])*100),
Analytics_Data_Presto[Date]>=cur_start && Analytics_Data_Presto[Date]<=cur_end)
),
CALCULATE(
(SUM('Analytics_Data_Presto'[Conversion Value])/sum(Analytics_Data_Presto[Conversions])),
Analytics_Data_Presto[Date]>=cur_start && Analytics_Data_Presto[Date]<=cur_end)
)
)
)
)))))Hi, Anonymous
You may try replacing 'ALL' with 'ALLSELECTED' to see if it works.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
