Cards to show previous period and/or exclude current period

[Anonymous]

I have a custom calendar table linked to my data, which allows me to aggregate my data into period of custom Weeks, or Months, Custom Financial quarters etc. I then have sales and performance data which I've related to the custom calendar.

I'm building a dashboad to show period performance at whichever level I select. So my charts are showing period on period tracking for each of the measures.

Now I want to use a Card Visual to show the previous periods total, so that every time my data refreshes and we've moved into a new period, it automatically filters itself to showing the previous period, for example, let's say previous week. How can I do this? At the moment I have to open up the dashboard every week/month and manually update all the Visual level filters across a number of cards.

And on my charts, I want to exclude the current (incomplete) period until we move out of it and it becomes complete, but I don't want to have to manually change the filters every time across a number of charts. Again, how can I do this?

I am hoping that makes sense and that someone can help! I'm surmising that it should be easy to put a current/previous week/month/quarter type flag into my custom table, which will refresh every time my data refreshes but I'm not sure what to use or how to then make it update the flag dynamically or if there's a better solution?

[v-ljerr-msft]

Hi @Anonymous,

I assume that you already have the Year and WeekNumber columns in your Calendar table. If not, you can use the formula below to add them first.

Year = YEAR ( 'CalendarTable'[Date] )

WeekNumber = WEEKNUM ( 'CalendarTable'[Date] )

Then, you should be able to use the formula below to create a measure to calculate the previous week sales, then show it on a Card visual. And you can use similar formulas to calculate sales for other previous periods.

Previous Week Sales =
VAR currentWeek =
    MAX ( 'CalendarTable'[WeekNumber] )
VAR currentYear =
    MAX ( 'CalendarTable'[Year] )
RETURN
    CALCULATE (
        [Total Sales],
        FILTER (
            ALL ( CalendarTable ),
            'CalendarTable'[Year] = currentYear
                && 'CalendarTable'[WeekNumber]
                = currentWeek - 1
        )
    )

Regards

[Anonymous]

This is great, thanks. Just one question, how would i build in an if statement to say if the curent week is week 1, use previous year, week 52?

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

I just forgot that important part. Try the formula below.

Previous Week Sales =
VAR currentWeek =
    MAX ( 'CalendarTable'[WeekNumber] )
VAR currentYear =
    MAX ( 'CalendarTable'[Year] )
RETURN
    IF (
        currentWeek = 1,
        CALCULATE (
            [Total Sales],
            FILTER (
                ALL ( CalendarTable ),
                'CalendarTable'[Year]
                    = currentYear - 1
                    && 'CalendarTable'[WeekNumber] = 52
            )
        ),
        CALCULATE (
            [Total Sales],
            FILTER (
                ALL ( CalendarTable ),
                'CalendarTable'[Year] = currentYear
                    && 'CalendarTable'[WeekNumber]
                    = currentWeek - 1
            )
        )
    )

Regards