I ahve created a Unique Query ID and i have tried using the following formula it gave error
ClicksDifference =
VAR CurrentQueryID = 'DS Page And Query Wise'[UniqueID]
VAR UnderscorePosition = FIND("_", CurrentQueryID, 1, LEN(CurrentQueryID)) // Find the first underscore position
// Extract current month substring after the last underscore
VAR CurrentMonth =
MID(
CurrentQueryID,
UnderscorePosition + 1, // Start from the character after the underscore
LEN(CurrentQueryID) - UnderscorePosition // Length of the substring
)
// Assuming you have a mapping table for months like in the previous example
VAR MonthMapping =
DATATABLE (
"MonthName", STRING,
"MonthNumber", INTEGER,
{
{"January", 1}, {"February", 2}, {"March", 3}, {"April", 4},
{"May", 5}, {"June", 6}, {"July", 7}, {"August", 8},
{"September", 9}, {"October", 10}, {"November", 11}, {"December", 12}
}
)
// Lookup current month number
VAR CurrentMonthNumber = LOOKUPVALUE(MonthMapping[MonthNumber], MonthMapping[MonthName], CurrentMonth)
// Calculate previous month number
VAR PreviousMonthNumber = IF(CurrentMonthNumber = 1, 12, CurrentMonthNumber - 1)
// Lookup previous month name
VAR PreviousMonthName = LOOKUPVALUE(MonthMapping[MonthName], MonthMapping[MonthNumber], PreviousMonthNumber)
// Replace current month with previous month in QueryID
VAR PreviousQueryID = SUBSTITUTE(CurrentQueryID, "_" & CurrentMonth, "_" & PreviousMonthName)
// Calculate previous month clicks
VAR PreviousClicks =
CALCULATE(
SUM('DS Page And Query Wise'[Url Clicks]),
FILTER(
'DS Page And Query Wise',
'DS Page And Query Wise'[UniqueID] = PreviousQueryID
)
)
// Calculate current month clicks
VAR CurrentClicks = SUM('DS Page And Query Wise'[Url Clicks])
// Calculate clicks difference
RETURN
IF(
ISBLANK(PreviousClicks),
BLANK(),
CurrentClicks - PreviousClicks
)
Second Query I used
Clicks Difference =
VAR SelectedClicks = 'DS Page And Query Wise'[Url Clicks]
VAR SelectedLOB = 'DS Page And Query Wise'[LOB ]
VAR SelectedQuery = 'DS Page And Query Wise'[Query]
VAR SelectedLandingPage = 'DS Page And Query Wise'[Landing Page]
VAR SelectedDate = 'DS Page And Query Wise'[Date (Year Month)]
VAR PriorMonth = EOMONTH(SelectedDate, -1)
VAR PreviousMonthClicks =
CALCULATE(
SUM('DS Page And Query Wise'[Url Clicks]),
FILTER(
'DS Page And Query Wise',
'DS Page And Query Wise'[Date (Year Month)] = PriorMonth &&
'DS Page And Query Wise'[LOB ] = SelectedLOB &&
'DS Page And Query Wise'[Query] = SelectedQuery &&
'DS Page And Query Wise'[Landing Page] = SelectedLandingPage
)
)
RETURN
IF( 'DS Page And Query Wise'[LOB ] = SelectedLOB &&
'DS Page And Query Wise'[Query] = SelectedQuery &&
'DS Page And Query Wise'[Landing Page] = SelectedLandingPage &&
'DS Page And Query Wise'[Date (Year Month)] = PriorMonth,
SelectedClicks - PreviousMonthClicks,
BLANK()
)
