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

Anonymous's avatar
Anonymous
Not applicable
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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank you Anonymous! 

 

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).