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 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
Solved! Go to Solution.
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.
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 Regards
Allan
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.
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
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
@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/1051123
relationship with main table and dates tables
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |