Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |