Dynamic comparison tables using separate date filters

[Anonymous]

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

[v-alq-msft]

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

[v-alq-msft]

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.

[Anonymous]

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)

 )

 )

 )

 )))))

[v-alq-msft · Accepted 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.

[Anonymous]

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

[amitchandak]

@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

[Anonymous]

relationship with main table and dates tables