SelectedValue (slicer[TYPE]) Today, Yesterday, MTD, etc

[PaulW]

Hello, I have having a lot of trouble with a date period selection. I’ve just got started on DAX, I must say that I stole this code from another forum ( https://community.powerbi.com/t5/Power-Query/How-to-calculate-MTD-last-7-days-Yesterday-today-date-range/m-p/731354#M24307)

  Time Period = 
    VAR tod =
        TODAY()
    VAR yd =
        YEAR( TODAY() )
    VAR md =
        MONTH( TODAY())
    VAR yest = tod - 1
    VAR last7 = tod - 7
    VAR sele =
        SELECTEDVALUE( Slicer[TYPE] )
    VAR todaysales =
        CALCULATE(
            SUM( Dates[Date] ),
            FILTER(Dates,'Dates'[Date] = tod )
        )
    VAR yestsales =
        CALCULATE(
            SUM(Dates[Date]),
            FILTER(Dates, 'Dates'[Date] <= tod && 'Dates'[Date] > last7 )
        )
    VAR last7sales =
        CALCULATE(
            SUM('Dates'[Date]),
            FILTER('Dates', 'Dates'[Date] <= tod && 'Dates'[Date] > last7 )
        )
    VAR mtdsale =
        CALCULATE(
            SUM('Dates'[Date]),
            FILTER(
                'Dates',
                YEAR('Dates'[Date]) = yd
                && MONTH('Dates'[Date]) = md
                && 'Dates'[Date] <= tod
            )
        )
    RETURN 
        IF(
            ISFILTERED ( Slicer[TYPE] ),
                SWITCH(
                    TRUE(),
                    sele = "Today", todaysales,
                    sele = "Yesterday", yestsales,
                    sele = "Last 7 Days", last7sales,
                    sele = "MTD", mtdsale
                )
        )

My Slicer table looks like this (to be honest I do not know if I have done this correctly)

Slicer = {
    (1,"Today"),
    (2,"Yesterday"),
    (3,"Last 7 Days"),
    (4,"MTD")
}

If I drag a slicer onto the canvas and put ‘Type’ into the ‘Filters on this visual’, it appears right on the screen, but when I make a selection nothing changes, all the dates come up. I should also mention that the slicer table is not joined to anything, I’m not sure what I’d join it on considering the date table is in the code.

This is the code for the date table I created:

Dates = 
VAR BaseCalendar =
    CALENDARAUTO(6)
RETURN
    GENERATE(
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate = YEAR( BaseDate )
        VAR MonthNumber = MONTH( BaseDate )
        RETURN ROW(
            "Day", BaseDate,
            "Year", YearDate,
            "Month Number", MonthNumber,
            "Month", FORMAT( BaseDate, "mmmm"),
            "Year Month", FORMAT( BaseDate, "mmm yy"),
            "Year QTR", FORMAT( BaseDate, "\QTR q")
        )
    )

It is my first time posting here, I’ve been a member for a few years, but could not get any Power BI work in that time (would you believe!), I’m enjoying it, but this is killing me!

Thank you in advance for any help, happy to provide an additional info as needed. I’m on Australia time.

Regards,
P.

[AntrikshSharma]

You are using naked reference to the Date table, with your scenario FILTER only returns one date for each row of the matrix, and that date is the same date visible on the matrix, use ALL ( Dates ) to remove any filters from the date table and to get full date table at each row of the matrix so that then you can filter that list by using your conditions.

The community post works because he isn’t filtering date table, I didn’t read the full post but that appears to be a table on the many side of the relationship.

[BrianJ]

@PaulW,

Welcome to the forum - great to have you here!

Rather than revising your DAX code, I’m going to propose a different and I think better approach to incorporating a period slicer:

1. Start with @Melissa’s incredible Extended Date Table. This is the most powerful and flexible date table I’ve ever seen and it has become the de facto standard within the Enterprise DNA Community.

2. On top of that, apply the period slicer approach that @JarrettM posted in the M code showcase.

If you need an example of this approach in action, check out @greg’s entry for Data Challenge #5 .

I hope this is helpful.

• Brian

[PaulW]

Hi, thank you for responding, I kind of get what you are saying, but I am not sure where to make the changes, is it here somewhere? ‘All’ is already there, but I do not think this is what you are referring to. Also, I think the '1’s down the side might be having an effect as well. In the table I put an index and asked PBI to sort using the index, but it does not change the order of the choices.

Regards,
Paul.

[PaulW]

Hi Brian,

Thank you for this, I am going to look into this, but I need to understand why what I have done has not worked for important learning purposes, plus it will be nice to fix what have and show people, then ‘upgrade’ to what you have suggested here.

Regards,
Paul.

[Martin]

Hi Paul,

You need to put your [Time Period] measure into the table on your report page, not fields from the date table. Or use a card visual with your measure since it returns only one sales amount anyway. Since you use TODAY as the reference date in your measure code and you define the periods in your measure code as well you cannot set a different date context for this measure. Vice versa your measure does also not affect the date context of your report page, thus your table shows all the dates. You only have to make sure that you do not set a date context that hides days that are needed in your measure or, as mentioned by @AntrikshSharma , explicitly add ALL(‘Dates’) to you measure.

The"1"s in your Filters mean: "There is one occurence of “Last 7 Days” in your ‘Slicer’ table, there is one occurence of “MTD” in your ‘Slicer’ table and so on. This does not affect the behavior of your report, it’s only for information purpose. There is no custom sorting in the Filters pane. You could use a slicer on your report page instead, there sorting works. Or you include 1., 2., 3. … in the name of your options so they sort as you prefer, but then you also need to adjust your Measure code if you refer to the names there.

Ultimately I’d also prefer the approach described by @BrianJ .

BR
Martin

[PaulW]

Hi Martin,

Thank you for taking the time to reply, I see what you mean about the single date. I need to start this again. I have created the table that Brian mentioned ( I followed the guide to turn it into a table as well), I do not know any ‘M’, and I am trying to work out what I need to do to set the Australian FY correctly, I have tried several things but it always errors out. It is advanced for me, I’m not sure how to change those things.

I just need to get the date table straight for the Aussie FY (and eventually enrich it with holidays).
Have a slider or radio button option that will change the visuals to show the last week, and another for the last 2 weeks Sunday to previous Monday.

Something like this…

I should have mentioned those goals in the first place, it most likely would have helped you help me, sorry about that. These are the current goals I need to meet, I have read a lot and there is more out there than I know what to do with and I might be suffering with too much information. I was hoping to find something straight forward on Enterprise DNA, I may not be looking in the right place. Brian’s suggestions look solid, but I’m not sure if I know enough to be able to use/employ it effectively. I am determined to get this working one way or another.

Regards,
Paul.

[PaulW]

Hi Brian, I just do not understand how this is working. I read Chris’ blog, and all the instructions, when I create the two sets of ‘M’ code, I do not end up with tables, I just end up with…

I"m not getting this, and I’ve lost a day on it. Why am I not getting tables?

Kind regards,
Paul.

[Melissa]

Hi @PaulW,

Have you tried using the nested version you can find here
Just paste it in a new Blank Query and the output will be a Period table.

I hope this is helpful

[PaulW]

Hi @Melissa , thank you, but the result is exactly the same.

Why is this working for everyone but me? I dont understand the difference.

Regards,
Paul.

[Melissa]

Hi @PaulW,

You need to go to Power Query first, follow these steps:

1. Go to “Transform Data” on the Home tab in Power BI Desktop
2. New Source / Blank Query on the Home tab in Power Query
3. Open the “Advanced Editor”
4. Select everything and Paste in the code provided

I hope this is helpful

[PaulW]

Melissa, that is crazy! Can you tell me why placing it in advanced editor instead of the formula bar makes the difference, please?

Thank you,
Regards,
Paul.

[PaulW]

@BrianJ

Hi Brian, would you please be kind enough to redo the link for the period slicer approach, please? I have had a really good look for it but I cant find it. I think its just missing the ‘@’.

Thank you.

Regards,
Paul.

[BrianJ]

@PaulW,

Here you go:

Dynamic Date Range Slicer- Query M

All, Here is a solution that I use on most of my models regarding a dynamic date range slicer. I found the solution from Chris Webb, from this blog post: I changed up his solution a bit to match the solution I wanted. In order to set this up, you’ll need 2 different sets of M code. Read the blog to understand fully how this is done, but I wanted to point out that an important feature in the setup is the Relationship between the Table and the “Date Table”, Make sure in this case to have the Cr…

Looks like you’re making a good day’s worth of progress.

• Brian

[PaulW]

Thank you, @BrianJ

‘Looks like you’re making a good day’s worth of progress.’

HA! Yes! But now I have having trouble with the filtering!

Regards,
Paul.

[BrianJ]

@PaulW,

Did you download @greg’s Challenge #5 PBIX file? He has the period filtering set up perfectly with the extended date table there, so it should give you a good example to follow.

• Brian

[PaulW]

I didnt actually, I will now though and check it out. Thanks @BrianJ have a great weekend!

Regards,
Paul.

[PaulW]

I have to give up on this, I do not understand why this works for other people but not for me. I have read the blogs several times over, the choosing of the buttons has no effect on any of the visuals in my report.

Regards,
Paul.

[AntrikshSharma]

Did you actually try what I suggested? Using ALL ( Dates ) also why are you summing Dates?

Time Period =
VAR tod =
    TODAY ()
VAR yd =
    YEAR ( tod )
VAR md =
    MONTH ( tod )
VAR yest = tod - 1
VAR last7 = tod - 7
VAR sele =
    SELECTEDVALUE ( Slicer[TYPE] )
VAR todaysales =
    CALCULATE ( SUM ( Dates[Date] ), FILTER ( ALL ( Dates ), 'Dates'[Date] = tod ) )
VAR yestsales =
    CALCULATE (
        SUM ( Dates[Date] ),
        FILTER ( ALL ( Dates ), 'Dates'[Date] <= tod && 'Dates'[Date] > last7 )
    )
VAR last7sales =
    CALCULATE (
        SUM ( 'Dates'[Date] ),
        FILTER ( ALL ( Dates ), 'Dates'[Date] <= tod && 'Dates'[Date] > last7 )
    )
VAR mtdsale =
    CALCULATE (
        SUM ( 'Dates'[Date] ),
        FILTER (
            ALL ( Dates ),
            YEAR ( 'Dates'[Date] ) = yd
                && MONTH ( 'Dates'[Date] ) = md
                && 'Dates'[Date] <= tod
        )
    )
RETURN
    IF (
        ISINSCOPE ( Slicer[TYPE] ),
        SWITCH (
            TRUE (),
            sele = "Today", todaysales,
            sele = "Yesterday", yestsales,
            sele = "Last 7 Days", last7sales,
            sele = "MTD", mtdsale
        )
    )

[BrianJ]

@PaulW,

The best way to get a solution from the forum is always to post a PBIX file. Here’s a video on a simple approach to anonymizing and reducing your data for posting on the forum. I’m 100% confident that if you can provide a file representative of your data and data model that we can work out what the problem is.

How To Mask Sensitive Data In Power BI

• Brian