Var Dateadd Alternative

[Anonymous]

The Why: So I am building out a visualization that leverages time as a filter/slicer to pick between two points in time. From that filtering/slicer of time (which should be this year), I'm looking to see a like for like comparison between the slicer option, and 1 year ago, 2 years ago, 3 years ago, etc.

The How: Basically, if I were to pick a date range between 8/1/2018 - 12/31/2018 I would want to have a series of values summed for 8/1/2017 - 12/31/2017 (-1 Year) next to a series of values summed for 8/1/2018 - 12/31/2018. Or if I wanted to do -2 Years, it would be a range of 8/1/2016 - 12/31/2018, with the summed "burn rate" for that time frame.

Note: The way I want to have the filter used in a way where the users pick the date value for the period in question, and in one chart they see the Selected Period's "burn rate" in one chart, then in a separate chart see the Current Year's "burn rate" in another chart. I've tried a few ways to work through this one, but with the variable of the calculation makes for DATEADD a little complex, it's not happy about using a variable, or a measure of max/min time in a preceding variable equation.

My Attempts to Resolve: I've been trying to wrap my head around a way to bake this in, but I'm having a larger issue around getting the DAX to accept a variable or giving me something about a MIN calculation not being feasible with a calculated measure. Any thoughts would be appreciated as I've documented below my different tries to no avail. 

1. I tried using the same logic, but just pushing a DATEADD for the min/max -1, -2, -3. 

Selected Period's "burn rate" = 
CALCULATE(SUM(Table1[burn rate]),USERELATIONSHIP('CALENDAR'[Date],Table1[date]))

Selected Period's "burn rate" - 1 YR = 
CALCULATE(SUM(Table1[burn rate]),USERELATIONSHIP('CALENDAR'[CY-1],Table1[date]))

In this situation, 'Calendar'[CY-1] was a column generated from [Date] with the formula = DATEADD('Calendar'[Date],-1,YEAR)

Error: USERELATIONSHIP function can only use the two columns references participating in a relationship

2. I also thought about using the calculation you had recommended for doing the "sameperiodTHISyear" filtering, but just in reverse, and incorporating the DATEADD so I could go back one year, that didn't work either.

Current Year's "burn rate" - 1 = 

var mind =CALCULATE(MIN('CALENDAR'[Date]))
var maxd = CALCULATE(MAX('CALENDAR'[Date]))

var yearmin = DATEADD(mind,-1,Year)
var yearmax = DATEADD(maxd,-1,Year)

return

CALCULATE(SUM(Table1[burn rate]),
FILTER(Table1,Table1[date]>=yearmin && Table1[date]<=yearmax))

Error: The first argument to 'DATEADD' must specify a column. 

Neither of these worked, I'm trying to go through and think about it may be another way, but in my use case I'm restricted by 2 things: 

1) I need to be able to do not just the current year, and current year-1, but also current year-2, -3, -4, etc. 

2) I have more than 1 Table1 type of "burn rate" value that I want to link to the CALENDAR[Date] Slicer. 

Also, where I am now is based on something that I had tried in an earlier post, "sameperiodTHISyear Time Filtering": https://community.powerbi.com/t5/Desktop/sameperiodTHISyear-Time-Filtering/m-p/578109

There's a sample PBIX in the above URL to the reply I got from a community contributor. 

Your help would be greatly appreciated, there's an upvote/marked as solution for anyone that can help me out! 

[MarkPalmberg]

I'm just here to say I came across this issue because I couldn't figure out why I can't use a VAR with the DATEADD function in creating a date table, and I wanted to say this is one of the best-documented questions I've come across in a long time. Thanks for the inspiration. 

[TW]

Why not use EDATE()?

[v-shex-msft · Accepted solution]

Hi @Anonymous,

I modify your formula to use date function to get corresponded previous date, you can try it if it suitable for your requirement:

Current Year's "burn rate" - 1 =
VAR mind =
    MINX ( ALLSELECTED ( 'CALENDAR'[Date] ), [Date] )
VAR maxd =
    MAXX ( ALLSELECTED ( 'CALENDAR'[Date] ), [Date] )
RETURN
    CALCULATE (
        SUM ( Table1[burn rate] ),
        FILTER (
            Table1,
            Table1[date]
                >= DATE ( YEAR ( mind ) - 1, MONTH ( mind ), DAY ( mind ) )
                && Table1[date]
                    <= DATE ( YEAR ( maxd ) - 1, MONTH ( maxd ), DAY ( maxd ) )
        )
    )

In addition, if you want these formula can be dynamic change based on slicer, you can add a what-if parameter table with numeric value, then try modify above formula to use what-if parameter table selected value as unit.

Current Year's "burn rate" - 1 =
VAR mind =
    MINX ( ALLSELECTED ( 'CALENDAR'[Date] ), [Date] )
VAR maxd =
    MAXX ( ALLSELECTED ( 'CALENDAR'[Date] ), [Date] )
VAR Para =
    SELECTEDVALUE ( ParaTable[Value] )
RETURN
    CALCULATE (
        SUM ( Table1[burn rate] ),
        FILTER (
            Table1,
            Table1[date]
                >= DATE ( YEAR ( mind ) - Para, MONTH ( mind ), DAY ( mind ) )
                && Table1[date]
                    <= DATE ( YEAR ( maxd ) - Para, MONTH ( maxd ), DAY ( maxd ) )
        )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you @v-shex-msft! 

So I got hung up trying to do a DATEADD or some type of Function to do a full date change, but looks like I just needed to do something as simple as -1, -2, -3. 

I would say that the parameter was something that crossed my mind, but for my use case I would want to be able to reflexively do a rolling -1, -2, -3 years based on the min/max of the current date. Still, the parameterization is very cool!

I saw an interesting post in the community around leveraging parameters to define min/max date ranges in Power BI, but the biggest caveat with that is that months can have anywhere between 28-31 days so the functionality wasn't fully there, a future feature request would be to allow What-If Parameters that were Data Typed as Calendar Months/Days/Years (or dates as a whole). 

[Anonymous]

@v-frfei-msft If you would have any thoughts around this it would be greatly appreciated!