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 )
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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 160 members 1,692 guests
Please welcome our newest community members: