Getting Date Filter to filter between monthly and yearly value

[kboud3]

I have a table that shows every sale or cancellation for the company. We are filtering this data by date, sales consultant, and/or area. We want to see how monthly/yearly numbers measure vs the budget. Each sales consultant has a different monthly/yearly budget for sales, meetings, sales dollars, and cancellations. I had to manually create a new table and input the four sales consultant along with their appropriate budget. My created table looks something like this:

Sales Consultant | Monthly FFF Bud | Yearly FFF Bud | Monthly Sales Dollars Bud | Yearly Sales Dollars Bud | ....

Name 1                        4.38                             52.5                               1,312,705                            15,752,460

Name 2                        2.5                                  30                                785,365                               9,424,380

Name 3                        1.25                               15                                    362,816                              4,353,792

The date range that is connected to a created date table which is connected to the sale/cancel date has this formula:

NewSpecialDates =

VAR _datetable = NewDateRange

VAR _today = TODAY()

VAR _month = MONTH(TODAY())

VAR _year = YEAR(TODAY())

VAR _thismonthstart = DATE(_year,_month,1)

VAR _thisyearstart = DATE(_year,1,1)

VAR _lastmonthstart = EDATE(_thismonthstart,-1)

VAR _lastmonthend = _thismonthstart-1  

VAR _thisquarterstart = DATE(YEAR(_today),SWITCH(true,_month>9,10,_month>6,7,_month>3,4,1),1)

RETURN UNION(

    ADDCOLUMNS(FILTER(_datetable,[Date]=_today),"Period","Today","Order",1),

    ADDCOLUMNS(FILTER(_datetable,[Date]=_today-1),"Period","Yesterday","Order",2),

    ADDCOLUMNS(FILTER(_datetable,[Date]>_today-7),"Period","Last 7 Days","Order",3),

    ADDCOLUMNS(FILTER(_datetable,[Date]>=_today-14),"Period","Last 2 Weeks","Order",4),

    ADDCOLUMNS(FILTER(_datetable,[Date]>=_thisquarterstart),"Period","This Quarter","Order",5),

    ADDCOLUMNS(FILTER(_datetable,[Date]>=_thisyearstart),"Period","This Year","Order",6),

    ADDCOLUMNS(FILTER(_datetable,[Date]>_today-30),"Period","Last 30 Days","Order",7),

    ADDCOLUMNS(_datetable,"Period","Custom Range","Order",8)

    )

I created an IF statement to filter between the monthly and yearly FFF budget but the values do not switch from monthly to yearly. Not sure what I should do next. This is my IF statement: 

New FFF Bud =

IF(SELECTEDVALUE('NewSpecialDates'[Period])="Custom Range",DIVIDE('Budget Table'[Yearly FFF Bud],365)*DATEDIFF(FIRSTDATE('NewSpecialDates'[Date]),LASTDATE('NewSpecialDates'[Date]),DAY),IF(SELECTEDVALUE('NewSpecialDates'[Period])="Last 30 Days",'Budget Table'[Monthly FFF Bud],IF(SELECTEDVALUE(NewSpecialDates[Period])="This Year",'Budget Table'[Monthly FFF Bud]*12,IF(SELECTEDVALUE('NewSpecialDates'[Period])="This Quarter",'Budget Table'[Monthly FFF Bud]*3,IF(SELECTEDVALUE('NewSpecialDates'[Period])="Last 2 Weeks",'Budget Table'[Monthly FFF Bud],IF(SELECTEDVALUE('NewSpecialDates'[Period])="Last 7 Days",'Budget Table'[Monthly FFF Bud],IF(SELECTEDVALUE('NewSpecialDates'[Period])="Today",'Budget Table'[Monthly FFF Bud],IF(SELECTEDVALUE('NewSpecialDates'[Period])="Yesterday",'Budget Table'[Monthly FFF Bud],DIVIDE('Budget Table'[Yearly FFF Bud],12)))))))))

[lbendlin]

You may want to teach your sales consultants how to use the filter pane and how to set relative or absolute date filters. That might remove the need for all these special filters.

Have a look at DAXFormatter.com, it helps you better format your code.

New FFF Bud =
IF(
    SELECTEDVALUE( 'NewSpecialDates'[Period] ) = "Custom Range",
    DIVIDE( 'Budget Table'[Yearly FFF Bud], 365 )
        * DATEDIFF(
            FIRSTDATE( 'NewSpecialDates'[Date] ),
            LASTDATE( 'NewSpecialDates'[Date] ),
            DAY
        ),
    IF(
        SELECTEDVALUE( 'NewSpecialDates'[Period] ) = "Last 30 Days",
        'Budget Table'[Monthly FFF Bud],
        IF(
            SELECTEDVALUE( NewSpecialDates[Period] ) = "This Year",
            'Budget Table'[Monthly FFF Bud] * 12,
            IF(
                SELECTEDVALUE( 'NewSpecialDates'[Period] ) = "This Quarter",
                'Budget Table'[Monthly FFF Bud] * 3,
                IF(
                    SELECTEDVALUE( 'NewSpecialDates'[Period] ) = "Last 2 Weeks",
                    'Budget Table'[Monthly FFF Bud],
                    IF(
                        SELECTEDVALUE( 'NewSpecialDates'[Period] ) = "Last 7 Days",
                        'Budget Table'[Monthly FFF Bud],
                        IF(
                            SELECTEDVALUE( 'NewSpecialDates'[Period] ) = "Today",
                            'Budget Table'[Monthly FFF Bud],
                            IF(
                                SELECTEDVALUE( 'NewSpecialDates'[Period] ) = "Yesterday",
                                'Budget Table'[Monthly FFF Bud],
                                DIVIDE( 'Budget Table'[Yearly FFF Bud], 12 )
                            )
                        )
                    )
                )
            )
        )
    )
)

You can use the SWITCH() statement to make the nested IF statements even more readable.

New FFF Bud =
SWITCH(SELECTEDVALUE( 'NewSpecialDates'[Period] ),
"Custom Range",    DIVIDE( 'Budget Table'[Yearly FFF Bud], 365 )
        * DATEDIFF(
            FIRSTDATE( 'NewSpecialDates'[Date] ),
            LASTDATE( 'NewSpecialDates'[Date] ),
            DAY
        ),
"Last 30 Days",  'Budget Table'[Monthly FFF Bud],
"This Year",     'Budget Table'[Monthly FFF Bud] * 12,
"This Quarter",  'Budget Table'[Monthly FFF Bud] * 3,
"Last 2 Weeks",  'Budget Table'[Monthly FFF Bud],
"Last 7 Days",   'Budget Table'[Monthly FFF Bud],
"Today",         'Budget Table'[Monthly FFF Bud],
"Yesterday",      'Budget Table'[Monthly FFF Bud],
DIVIDE( 'Budget Table'[Yearly FFF Bud], 12 )
)

It also shows you where the code needs to be adjusted.