I created a line graph that shows the current "MTD"(1st line) and "PY MTD"(2nd line) of Gross Profit(y-axis) by "Week of Month" (e.g. 1,2,3,4 and 5 depending on the month). A custom, fiscal Calendar dim is being leveraged where the start date and end date of each fiscal year can vary. There are two very similiar slicer scenarios that I'm trying to account for with the line graph that I'm trying to figure out the DAX for. Also, when I say "PY MTD", I mean the previous year's month from start to end, not part of the month beginning from the start of the month and up to a certain date within that same month.
To explain my issue, I have converted the line graph into a table visual.
When the report initially loads, the report is defaulted to the current month and year(e.g May 2022). The field that drives this is called "Default Month"(e.g "Yes") which is part of the Calendar Dim. Note that this "Default Month" configuration yields the same results if the "Month" slicer was set to May and the "Year" slicer was set to 2022.
Under this scenario, the "MTD" values are correct for each "Week of Month" number but the "PY MTD" value is incorrect only for week 5...the correct value should be 99,845(week 5 of May 2021) instead of -366 which I'm not sure where that is coming from.
- Week 5 for May 2022 in the calendar dim starts on 5/23 and ends on 5/29 which why "MTD" doesn't show a value for week 5 as the [Data Effective Date] = 5/19/2022, which falls in Week 4. This blank value for week 5 is desired.
DAX for [v_MaxWeek]
- The value for the [Data Effective Date] is...5/19/2022 which drives the DAX calcs.
v_MaxWeek = VAR MaxData = [Data Effective Date] RETURN MAXX(FILTER('Dim Calendar','Dim Calendar'[Date] <= MaxData),'Dim Calendar'[Week ID])
DAX for "MTD" measure...
Graph - Gross Profit (MTD) = VAR vMaxWeek = [v_MaxWeek] RETURN IF ( ISBLANK(vMaxWeek) = FALSE(), CALCULATE ( [Transaction Amt GP*], ALL ( 'Dim Calendar' ), 'Dim Calendar'[Week ID] = vMaxWeek ), BLANK() )
DAX for [v_PrevYearMaxWeekofYear]
- This is to account for years that either have 52 or 53 weeks and subtracts from the [v_MaxWeek] to get the previous year's "Week of Year" number.
v_PrevYearMaxWeekofYear = VAR vPrevYear = [v_PrevYearMaxYear] RETURN MAXX(FILTER(ALL('Dim Calendar'),'Dim Calendar'[Year] = vPrevYear), 'Dim Calendar'[Week of Year])
DAX for "PY MTD" measure...
Graph - Gross Profit (PY MTD) = VAR vPrevYearMaxWeek = [v_MaxWeek] - [v_PrevYearMaxWeekofYear] RETURN CALCULATE ( [Transaction Amt GP*], ALL ( 'Dim Calendar' ), 'Dim Calendar'[Week ID] = vPrevYearMaxWeek )
Value that I'm expecting for "PY MTD" for week 5...
The second issue I'm experiencing is when the user clears out all filters so no slicer selections are made. Under this scenario, the values for "MTD" and "PY MTD" are BOTH incorrect only for week 5. The "MTD" value shows 423,458 instead of blank and the "PY MTD" value shows 206,632 instead of 99,845.
- The "MTD" value 423,458 is actually from week 5 of January 2022. This should be blank as week 5 is for May 2022.
- The "PY MTD" value 206,632(not sure where this is coming from) should be 99,845 instead.
Value that I'm expecting for "PY MTD" for week 5...
- As you can see, the "MTD" value shown in the above table for week 5 is actually from week 5 of January 2022.
Appreciate any help!
I'm reaching out to others to see if you could provide any additional guidance here please for the issuing I'm trying to resolve. As you can see, Amitchandak helped me get closer to the final solution but still have a little ways to go still. Let me know if you need more information to explain what I'm trying to achieve.
@Magellan2022 , With help from month Rank and week on month
MTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank]) && [Week of month] <=max([Week of month])))
LMTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1 && [Week of month] <=max([Week of month])))
Where month rank is new column
Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)
min week start of month = minx(filter('Date',[Month Year] =earlier([Month Year])),[Week Start date])
week of month = datediff([min week start of month],[date],Week)+1
So, I used your suggested DAX measures as guidance of creating my own version of the DAX for "MTD"..I used "=" instead of "<=" so each week wouldn't be running total. This approach works when the Month and Year slicers have values selected so this helped progress more in the right direction. However, I want to be able to produce the same results when no slicer values are selected(ie slicers cleared) but instead the Data Effective Date(5/19/2022) drives what Month and Year to calculate the gross profit for. Under this scenario, I would need to use variables in order to determine the max Month(based on the data effective date), max Year (based on the data effective date) and then perform the final "MTD" and "Prev Year MTD" calculations.
- Below is the new "MTD" DAX that works only when the Month and Year slicer values are selected.
- When no slicers are selected, then the resulting values are blank because the DAX calculating data into the future as no slicer values are selected for Month and Year.
- If I switch the DAX to use the commented out variables in the FILTER function, then the displayed results are correct except for week 5 as the value is coming from week 5 of January 2022 instead of week 5 of May 2022.
Graph - Gross Profit (MTD) = VAR vMaxWeekofMonth = [v_MaxWeekofMonth] VAR vMaxMonth = [v_MaxMonthID] RETURN CALCULATE ( [Transaction Amt GP*], FILTER ( ALL ( 'Dim Calendar' ), 'Dim Calendar'[Month ID] = MAX('Dim Calendar'[Month ID]) //vMaxMonth && 'Dim Calendar'[Week of Month] = MAX('Dim Calendar'[Week of Month]) //vMaxWeekofMonth ) )
- Below is the result after switching the DAX to use the variables instead of the MAX functions.
- The "MTD" value for week 5 below is from week 5 of January 2022 when it should be blank for week 5 of May 2022.
- The same issue is happening for "Prev Year MTD" where week 5 is incorrect as well where it should be 99,845.
Inside the measure "min week start of month", what does [Month Year] represent exactly for a value?...202205 or something else?
Click here to learn more about the September 2022 updates!
Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.
Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!