Unable to switch between showing data by month week, quarter or year

[StevenT]

Hi, I have a slicer that i want the user to choose to show data between week, month, quarter or year.

This slicer was created using these two tables

_PeriodSelector =

DATATABLE(

  "Period", STRING,

  {

    {"Week"},

    {"Month"},

    {"Quarter"},

    {"Year"}

  }

)

AND

_PeriodSelector2 = {

    ("Week", NAMEOF('Calendar'[Start of Week]), 0),

    ("Month", NAMEOF('Calendar'[Start of Month]), 1),

    ("Quarter", NAMEOF('Calendar'[Start of Quarter]), 2),

    ("Year", NAMEOF('Calendar'[Start of Year]), 3)

}

_PeriodSelector2 is what populates the slicer

Now I have a measure that I was hoping to use that says depending on what is chosen, calculate the results and show it in the matrix

Here's the code

Track Prev Sales =

VAR _period = SELECTEDVALUE(_PeriodSelector[Period], "Month")

VAR _lastDate = MAX(Calendar[Date])

RETURN

SWITCH(

    _period,

    "Week",

        CALCULATE(

            [Track Tot Sales $],

            DATESINPERIOD(Calendar[Date], _lastDate - 7, -7, DAY)

        ),

    "Month",

        CALCULATE(

            [Track Tot Sales $],

            DATESINPERIOD(Calendar[Date], EOMONTH(_lastDate, -1), -1, MONTH)

        ),

    "Quarter",

        CALCULATE(

            [Track Tot Sales $],

            DATESQTD(DATEADD(Calendar[Date], -1, QUARTER))

        ),

    "Year",

        CALCULATE(

            [Track Tot Sales $],

            DATESYTD(DATEADD(Calendar[Date], -1, YEAR))

        ),

    BLANK()

)

MY matrix table uses this measure and calculates the difference to get the previous value, it works when Month is chosen but not when anything else is.
In the code above, I confirmed that 
    VAR _period = SELECTEDVALUE(_PeriodSelector[Period], "Month")
is selecting the proper period.  But it only calculates the calc in the month section .  No matter which of the other 3 I choose.

"Month",

        CALCULATE(

            [Track Tot Sales $],

            DATESINPERIOD(Calendar[Date], EOMONTH(_lastDate, -1), -1, MONTH)

And so my matrix shows data corrrectly only when month is chosen. Here's what the matrix looks like

 this works only when month is chosen. The Prev $ is the Track Prev Sales measure.

So why won't it correctly calculate when week, quarter or Year is chosen?

Thanks, Steven Taub

[v-saisrao-msft]

Hi @StevenT,

Have you had a chance to review the solution we shared by @lbendlin? If the issue persists, feel free to reply so we can help further.

Thank you.

[StevenT]

Actually I think the better way to ask this question is the following.
Is there a way to set up my report so that a user can choose a date period off the slicer and have the matrix show the data with the correct period in the column and the difference between the previous date period?

Here's a better way of illustrating what I want to do.

Now currently i can choose from the slicer and the correct date periods appear.  Trouble is that only the month properly calculates the difference between timeperiods.
It ignores the other time periods.
I want to be able to have this properly calculated based on the period chosen.
At this time I'm ready to bag my current design and look at alternative ways of doing this. Any help is appreciated.

[v-saisrao-msft]

Hi @StevenT,

We haven’t heard back from you in a while regarding your issue. let us know if your issue has been resolved or if you still require support.

Thank you.

[v-saisrao-msft]

Hi @StevenT,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[lbendlin · Accepted solution]

The simplest way would be to use Visual Calculations to compute the comparison to the "prior" period, and to use Field Parameters to let the report user choose what the period grain should be.

[v-saisrao-msft]

Hi @StevenT,

I have recreated the issue with my sample data and included the PBIX file for your reference. If the problem persists, please attach the sample PBIX file as suggested by @lbendlin, so we can assist you further.

Hope this helps

Thank you.

[lbendlin]

Have your users asked for this?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...