Please help w/DAX

[jcampbell474]

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!

[RobbeVL]

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:

https://docs.microsoft.com/en-us/dax/parallelperiod-function-dax 

[jcampbell474]

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])

[jcampbell474 · Accepted solution]

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.

[jcampbell474]

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 )