This period versus previous period users select any time period

[Anonymous]

Hi, I want to calculate a metric eg "cost per click" for the time period selected and the previous period

Slightly more complicated as users can select any time period:

By way of explanation, logically this is along the lines of:

Step1: count number of days between "from_date" and "to_date" as "n" (where from and to dates is based on the date range selected by user)

Step 2: calculate the metric for previous period based on "from_date"-n to "to_date"-n

Thanks

[amitchandak]

Try. Make sure you have date table

Last Period =

var _max =maxx(date,date[date])
var _min =maxx(date,date[date])
var datediff1 = datediff(_min,_max,day)
var _maxX = _max-datediff1
var _minX = _min -datediff1
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date,date[date]<=_maxX &&date[date]>=_minX)))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

In case you need two ranges -https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[Anonymous]

Hi, thanks for your help @amitchandak 

I've got an error: ""Too few arguments were passed to the FILTER function. The minimum argument count for the function is 2".

Steps I've taken:

1) Created a "calander_table"

2) Linked it to my "data_table" using dates (all dates specified as dates as "dd/mm/yyyy")...nb: initially I linked data_table to calander table using multi-one, but I've also tried multi-multi too, not worked

3) Formula I'm using is:

Last Period =

var _max =maxx('Dates_Table','Dates_Table'[Date])

var _min =maxx('Dates_Table','Dates_Table'[Date])

var datediff1 = datediff(_min,_max,day)

var _maxX = _max-datediff1

var _minX = _min -datediff1

return

CALCULATE(SUM(Data_Table[Clicks]),filter(all('Dates_Table','Dates_Table'[Date]<=_maxX &('Dates_Table'[Date]>=_minX))))

Thanks

[amitchandak]

Try

CALCULATE(SUM(Data_Table[Clicks]),filter(all('Dates_Table'),'Dates_Table'[Date]<=_maxX &('Dates_Table'[Date]>=_minX)))

[Anonymous]

Thanks, that appears to work but I'm getting the following error message when I drop that into a card or table, as examples, are you able to advise? (the field is a value, decimal) @amitchandak 

Feedback Type:
Frown (Error)

Timestamp:
2020-02-20T11:59:20.0393234Z

Local Time:
2020-02-20T11:59:20.0393234+00:00

Session ID:
585ea86d-8f04-42b5-885c-d5cb8a6bff4f

Release:
December 2019

Product Version:
2.76.5678.782 (19.12) (x64)

Error Message:
MdxScript(Model) (12, 61) Calculation error in measure 'Data_Table'[Last Period]: DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

OS Version:
Microsoft Windows NT 10.0.18362.0 (x64 en-US)

CLR Version:
4.7 or later [Release Number = 528040]

Peak Virtual Memory:
38.7 GB

Private Memory:
586 MB

Peak Working Set:
713 MB

IE Version:
11.592.18362.0

User ID:
f3f54291-d14b-41d9-9b50-14cd4103e310

Workbook Package Info:
1* - en-GB, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

Snapshot Trace Logs:
C:\Users\Andy.Devine\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot2137450732.zip

Model Default Mode:
Import

Performance Trace Logs:
C:\Users\Andy.Devine\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_NewWebTableInference
PBI_showIncrementalRefreshPolicy
PBI_qnaLiveConnect
PQ_AIInsightsFunctionBrowser
PBI_realTimeRefresh
PBI_QueryDiagnostics
PBI_showMinervaRibbon
PBI_showMinervaViewNavigator
PBI_decompositionTree
PBI_themeDialog

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
100%

Supported Services:
Power BI

Formulas:

section Section1;

shared Data_Table = let
Source = Excel.Workbook(File.Contents("C:\Users\Andy.Devine\Desktop\Global\Shell\2. Search\This period vs last period test.xlsx"), null, true),
Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Impressions", Int64.Type}, {"Clicks", Int64.Type}})
in
#"Changed Type";

[Anonymous]

Hi, i have got it working using:

Last Period =

var _max =maxx('Dates_Table',[Date])

var _min =maxx('Dates_Table','Dates_Table'[Date])

var datediff1 = datediff(_min,_max,day)

var _maxX = _max-datediff1

var _minX = _min -datediff1

return

CALCULATE(SUM(Data_Table[Clicks]),filter(all('Dates_Table'),'Dates_Table'[Date]<=_maxX &&('Dates_Table'[Date]>=_minX)))

However, if I add this new calculated metric into a table or card, I get the exact same values as if I was just using [Clicks]

Are you able to help me some more?

Many thanks 

[amitchandak]

I have tried the same and worked for me with Date table.  The only diff is all selected.

Last period = 
var _min = minx(ALLSELECTED('Date'),('Date'[Date]))
var _max = maxx(ALLSELECTED('Date'),('Date'[Date]))
var _diff = DATEDIFF(_min,_max,DAY)+1
var _minx = _min-_diff
var _maxx = _max -_diff
Return
CALCULATE(SUM(Sales[Sales Amount]),filter(ALL('Date'),'Date'[Date]>=_minx && 'Date'[Date<=_maxx))