Skip to main content
cancel
Showing results for 
Search instead 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

Reply
jcampbell474
Helper IV
Helper IV

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

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
RobbeVL
Impactful Individual
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:

https://docs.microsoft.com/en-us/dax/parallelperiod-function-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])

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
Helper IV
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 )

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors