cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jcampbell474 Frequent Visitor
Frequent 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 Frequent Visitor
Frequent 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 Frequent Visitor
Frequent 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 Frequent Visitor
Frequent 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 Frequent Visitor
Frequent 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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

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.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 462 members 4,062 guests
Please welcome our newest community members: