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

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

     

    Thank you!

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