cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jcampbell474 Regular Visitor
Regular Visitor

Please help w/DAX

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

Accepted Solutions
jcampbell474 Regular Visitor
Regular Visitor

Re: Please help w/DAX

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.

View solution in original post

4 REPLIES 4
jcampbell474 Regular Visitor
Regular Visitor

Re: Please help w/DAX

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 )
Highlighted
RobbeVL Established Member
Established Member

Re: Please help w/DAX

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 Regular Visitor
Regular Visitor

Re: Please help w/DAX

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 Regular Visitor
Regular Visitor

Re: Please help w/DAX

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.

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (5,193)