Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Var Dateadd Alternative

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...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

MarkPalmberg's avatar
MarkPalmberg
Kudo Commander
5 years ago

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.