cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Showing Previous Month and Week Data

Hi Guys,

I need help on displaying data from previous month and week

As you can see from the attached file, I need to show previous week and month of meetings booked.

Screenshot

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Showing Previous Month and Week Data

Hi @tainguyen ,

You need a MonthNo column in ScoreCard table.

```MonthNo =
SWITCH (
TRUE (),
ScoreCard[Month] = "January", 1,
ScoreCard[Month] = "Febuary", 2,
ScoreCard[Month] = "March", 3,
ScoreCard[Month] = "April", 4,
ScoreCard[Month] = "May", 5,
ScoreCard[Month] = "June", 6,
ScoreCard[Month] = "July", 7,
ScoreCard[Month] = "August", 8,
ScoreCard[Month] = "September", 9,
ScoreCard[Month] = "October", 10,
ScoreCard[Month] = "November", 11,
ScoreCard[Month] = "December", 12
)```

Also, create a calculated table which lists unique Month names and Weeks. Make sure this table is unrelated to ScoreCard table.

```Slicer table =
CROSSJOIN ( VALUES ( ScoreCard[Week] ), VALUES ( ScoreCard[Month] ) )
```

Then, add below measures together with 'ScoreCard'[Trainee] into visual.

```Curr_Meetings =
CALCULATE (
SUM ( ScoreCard[Meetings] ),
FILTER (
ALLEXCEPT ( ScoreCard, ScoreCard[Trainee] ),
ScoreCard[Month] = SELECTEDVALUE ( 'Slicer table'[Month] )
&& ScoreCard[Week] = SELECTEDVALUE ( 'Slicer table'[Week] )
)
)prev_Meetings =VAR curr_Monthno =    CALCULATE (        MAX ( ScoreCard[MonthNo] ),        FILTER (            ALLSELECTED ( ScoreCard ),            ScoreCard[Month] = SELECTEDVALUE ( 'Slicer table'[Month] )        )    )RETURN    CALCULATE (        SUM ( ScoreCard[Meetings] ),        FILTER (            ALLEXCEPT ( ScoreCard, ScoreCard[Trainee] ),            ScoreCard[MonthNo] = curr_Monthno - 1                && ScoreCard[Week] = SELECTEDVALUE ( 'Slicer table'[Week] )        )    )```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super Contributor

## Re: Showing Previous Month and Week Data

Hi @tainguyen ,

You need a MonthNo column in ScoreCard table.

```MonthNo =
SWITCH (
TRUE (),
ScoreCard[Month] = "January", 1,
ScoreCard[Month] = "Febuary", 2,
ScoreCard[Month] = "March", 3,
ScoreCard[Month] = "April", 4,
ScoreCard[Month] = "May", 5,
ScoreCard[Month] = "June", 6,
ScoreCard[Month] = "July", 7,
ScoreCard[Month] = "August", 8,
ScoreCard[Month] = "September", 9,
ScoreCard[Month] = "October", 10,
ScoreCard[Month] = "November", 11,
ScoreCard[Month] = "December", 12
)```

Also, create a calculated table which lists unique Month names and Weeks. Make sure this table is unrelated to ScoreCard table.

```Slicer table =
CROSSJOIN ( VALUES ( ScoreCard[Week] ), VALUES ( ScoreCard[Month] ) )
```

Then, add below measures together with 'ScoreCard'[Trainee] into visual.

```Curr_Meetings =
CALCULATE (
SUM ( ScoreCard[Meetings] ),
FILTER (
ALLEXCEPT ( ScoreCard, ScoreCard[Trainee] ),
ScoreCard[Month] = SELECTEDVALUE ( 'Slicer table'[Month] )
&& ScoreCard[Week] = SELECTEDVALUE ( 'Slicer table'[Week] )
)
)prev_Meetings =VAR curr_Monthno =    CALCULATE (        MAX ( ScoreCard[MonthNo] ),        FILTER (            ALLSELECTED ( ScoreCard ),            ScoreCard[Month] = SELECTEDVALUE ( 'Slicer table'[Month] )        )    )RETURN    CALCULATE (        SUM ( ScoreCard[Meetings] ),        FILTER (            ALLEXCEPT ( ScoreCard, ScoreCard[Trainee] ),            ScoreCard[MonthNo] = curr_Monthno - 1                && ScoreCard[Week] = SELECTEDVALUE ( 'Slicer table'[Week] )        )    )```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 340 members 3,453 guests
Recent signins: