Forum Discussion
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!
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.
4 Replies
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 )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.
