Creating a Button to Switch between Custom time Intelligence

[Anonymous]

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 Question is - can you have a button to switch between these time periods rather than create measures for each of them. Below are some of my measures:

Fortnight:

FTD Sales Budget = CALCULATE([StaffedStoreBudget],FILTER(ALL(BBCalendar),BBCalendar[Fortnight]=MAX(BBCalendar[Fortnight])))

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

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

[v-luwang-msft]

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]

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.

[Anonymous]

This is exactly what i was after - just didnt know what it was called.

Thank you!

[amitchandak]

@Anonymous , Not very clear. Are you looking for a measure slicer?

measure slicer
https://www.youtube.com/watch?v=b9352Vxuj-M
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://www.youtube.com/watch?v=vlnx7QUVYME

[Anonymous]

following on from this.

Is there a way I can use the filter part of my calculate measure, as the slicer? This is what i have so far, trying to piece together different tutorials. the tutorial i was following creates a table that you can then use as the slicer:

TimePeriod Selection =

VAR TodayDate = MAX(SDAUSales[Date])

VAR SeasonToDate =

FILTER(

ALL(BBCalendar),

BBCalendar[FISCALCALENDARYEAR] = VALUES(BBCalendar[FISCALCALENDARYEAR])

&& BBCalendar[SeasonName] = VALUES(BBCalendar[SeasonName])

&& BBCalendar[Date]<=MAX(BBCalendar[Date])

)

VAR MonthToDate =

FILTER(

ALL(BBCalendar),

BBCalendar[FISCALCALENDARYEAR] = VALUES(BBCalendar[FISCALCALENDARYEAR])

&& BBCalendar[MonthName] = VALUES(BBCalendar[MonthName])

&& BBCalendar[Date]<=MAX(BBCalendar[Date])

)

VAR Result =

UNION(

ADDCOLUMNS(

CALENDAR(MonthToDate, TodayDate),

"Selection","MTD"

),

ADDCOLUMNS(

CALENDAR(SeasonToDate, TodayDate),

"Selection","STD"

)

)

Return

Result