Same Period Last Year/Month/Day based on level of drill down on Matrix/Bar Chart

[Anonymous]

Hi all, I am trying to create a measure that performs the following:

SUM (sales for CURRENT year/month/day) - 
SUM (sales for PREVIOUS year/month/day)

based on the level of drill down (Year/Month/Day) of a Matrix/Bar Chart/Line Chart/etc . I have been trying to figure this out for a while, but I am unable to make much progress.

Some of the obstacles I've faced:

SAMPERIODLASTYEAR only takes values from the previous year, which is not the calculation that I want when the drill down level is MONTH, or DAY.

DATEADD requires me to specity the INTERVAL of offset, which as far as I know I am unable to make dynamic just yet.

Has anyone had this problem before? Perhaps I should use a SWITCH function inside DATEADD, so that I am able to make the INTERVAL dynamic?

[Anonymous]

I thought of building a SWTICH function that takes True/False values from ISFILTERED, so that I can tell which level is the drilldown, 

ISFILTERED(DateTable[Date].[Year/Month/Day]) 

 but while the ISFILTERED function will return True when the drilldown level is, say, Month, it will ALSO return True when the drilldown level is Day.

If only it returns True ONLY WHEN it matches one, and only one, particular level of drilldown...

Here's a link to a sample file: 

https://1drv.ms/u/s!AsjXNd0CiWKAldsSIPz0AMRIWO4K6A

[Ashish_Mathur]

Hi,

In another tab of your PBI file, please show the exact result you are expecting.

[Anonymous]

If the drilldown level is Date, then ISFILTERED will return True ONLY when the drilldown level is Date

If the drilldown level is Month, then ISFILTERED will return True ONLY when the drilldown level is Month. Right now, ISFILTERED( DateTable[Date].[Month]) will also return True when the drilldown level is Date

If the drill down level is Quarter, then ISFILTERED will return True ONLY when the drilldown level is Quarter. Right now, ISFILTERED( DateTable[Date].[Month]) will also return True when the drilldown level is Quarter, Month and Date.

If the drill down level is Year, then ISFILTERED will return True ONLY when the drilldown level is Quarter. Right now, ISFILTERED( DateTable[Date].[Month]) will also return True when the drilldown level is Year, Quarter, Month and Date.

[iamprajot]

YY = CALCULATE(SUM(Data[Total]),DATEADD(Data[OrderDate].[Date],-1,YEAR))
QQ = CALCULATE(SUM(Data[Total]),DATEADD(Data[OrderDate].[Date],-1,QUARTER))
MM = CALCULATE(SUM(Data[Total]),DATEADD(Data[OrderDate].[Date],-1,MONTH))
DD = CALCULATE(SUM(Data[Total]),DATEADD(Data[OrderDate].[Date],-1,DAY))

Previous =
VAR Selected = SWITCH(TRUE(),
ISFILTERED(Data[OrderDate].[Year]),"YEAR",
ISFILTERED(Data[OrderDate].[Quarter]),"QUARTER",
ISFILTERED(Data[OrderDate].[Month]),"MONTH",
ISFILTERED(Data[OrderDate].[Day]),"DAY","Nothing")
RETURN
SWITCH(TRUE(),Selected="Year",[YY],Selected="Quarter",[QQ],Selected="Month",[MM],Selected="Day",[DD],"")

[Ashish_Mathur]

Hi,

I'd like to solve this problem from scratch.  Please share a small summy dataset and with rrspect to the numbers in that dataset, please show me your expected result.  If you wish, you may create the small dummy dataset in a simple Excel file and show your desired result in another tab of that Excel file.