Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Creating a Button to Switch between Custom time Intelligence

Hello All, I have a report that I need to switch between fortnight, month to date and 6month period to date(season). These time periods are based on a 445 calendar and are custom periods.   My Q...
Need Help
v-luwang-msft's avatar
v-luwang-msft
Community Support
4 years ago

Hi Anonymous ，

Have you test create a slicer table:

Slicer

Fortnight

Month

Season

Then use the below measure:

FTD Sales Budget =
IF (
    SELECTEDVALUE ( Slicer[slicer] ) = "Fortnight",
    CALCULATE (
        [StaffedStoreBudget],
        FILTER (
            ALL ( BBCalendar ),
            BBCalendar[Fortnight] = MAX ( BBCalendar[Fortnight] )
        )
    ),
    IF (
        SELECTEDVALUE ( Slicer[slicer] ) = "Month",
        IF (
            HASONEVALUE ( BBCalendar[FISCALCALENDARYEAR] )
                && HASONEVALUE ( BBCalendar[MonthName] ),
            CALCULATE (
                '.Measures'[StaffedStoreBudget],
                FILTER (
                    ALL ( BBCalendar ),
                    [FISCALCALENDARYEAR] = VALUES ( BBCalendar[FISCALCALENDARYEAR] )
                        && BBCalendar[MonthName] = VALUES ( BBCalendar[MonthName] )
                        && BBCalendar[Date] <= MAX ( BBCalendar[Date] )
                )
            ),
            BLANK ()
        ),
        IF (
            SELECTEDVALUE ( Slicer[slicer] ) = "Season",
            IF (
                HASONEVALUE ( BBCalendar[FISCALCALENDARYEAR] )
                    && HASONEVALUE ( BBCalendar[MonthName] ),
                CALCULATE (
                    '.Measures'[StaffedStoreBudget],
                    FILTER (
                        ALL ( BBCalendar ),
                        [FISCALCALENDARYEAR] = VALUES ( BBCalendar[FISCALCALENDARYEAR] )
                            && BBCalendar[MonthName] = VALUES ( BBCalendar[MonthName] )
                            && BBCalendar[Date] <= MAX ( BBCalendar[Date] )
                    )
                ),
                BLANK ()
            ),
            BLANK ()
        )
    )
)

 

 

Best Regards

Lucien

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks Lucien,

     

    But i need it to just be the time because I will have multiple values to change that sit in different table.

    Budget - Sits in a budget Table

    Sales - Sits in a Sales Table (different to the above)

    etc.

     

    So the idea would be I click MTD and it changes the value for sales and budget to the month to date.