cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

I'm relatively new to PBI & DAX, looking for some guidance with this expression.

Using a fiscal (master) calendar, I need to calculate sales for the previous week.  I have two tables, Fact and Master Calendar.  They're joined by calendar date (datekey field).  The previous year week is loaded into pbix, into a field.

Using the PreviousYearWeek already loaded, I need an expression that filters the Master Calendar to just the dates in the last fiscal week.  I.e., FiscalYearWeek = 201933.  This equals 8/3 - 8/9 in the Master Calendar table, which is joined to the Fact table.  I need those records filtered, too.  Should I be using CALCULATETABLE?

In Qlikview, I would simply use a short Set Analysis statement.  Again, pretty new to DAX.

Here's where I'm at:

```NBWTD =
CALCULATE (
SUM ( QMFact[BoundCount] ),
FILTER (
Master_Calendar,
Master_Calendar[FiscalYearWeek] = MAX ( DateReference[PrevYearWeek] )
)
)```

Any help will be appreciated.

Thanks!

1 ACCEPTED SOLUTION
Helper IV

Filtering the Master Calendar table (via DAX) in the expression didn't filter the Fact table, so I just added fiscal date columns to the fact table and applied the filter(s) there, using DAX.

It works fine.

4 REPLIES 4
Impactful Individual

Hi,

For the sales of a previous period I would simply use something like this:

```NBWTD =
CALCULATE (
SUM ( QMFact[BoundCount] ),
DIMDATE[Weeknr] = WEEK(TODAY())-1
)```

Another function you coud use:

Helper IV

Thank you.  I wish I could use native PBI date functions, but have a fiscal calendar.

FYI, I took another stab at it:

```NBPW =
VAR PYW =
SELECTEDVALUE ( DateReference[PrevYearWeek] )
RETURN
SUMX(CALCULATETABLE(QMFact,FILTER ( Master_Calendar, Master_Calendar[FiscalYearWeek] = PYW )), QMFact[NB])```
Helper IV

Filtering the Master Calendar table (via DAX) in the expression didn't filter the Fact table, so I just added fiscal date columns to the fact table and applied the filter(s) there, using DAX.

It works fine.

Helper IV

I also tried this.  Adding it to the post for clarification.

```NBWTD =
VAR PYW =
SELECTEDVALUE ( DateReference[PrevYearWeek] )
RETURN
CALCULATE (
SUM ( QMFact[BoundCount] ),
FILTER ( Master_Calendar, Master_Calendar[FiscalYearWeek] = PYW )```

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors