Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Magellan2022
Helper I
Helper I

Current Year MTD and PY MTD with Custom Fiscal Calendar Dim

Hello,

 

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.

 

Scenario 1

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.   

 

Table Visual

- 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.

Sc 1 - May 2022 Default - Table - MTD and PY MTD.png

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...

Sc 1 - May 2022 Default - Table - PY MTD Expected Value for Wk 5.png

 

Scenario 2

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.

 

Table Visual

- 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.

Sc 2 - Cleared Filters - Table - MTD and PY MTD.png

 

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.

Sc 1 - May 2022 Default - Table - PY MTD Expected Value for Wk 5.png

 

Appreciate any help!

4 REPLIES 4
Magellan2022
Helper I
Helper I

@Greg_Deckler , @Ashish_Mathur , @amitchandak 

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.

amitchandak
Super User
Super User

@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

@amitchandak 

 

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.

Magellan2022_1-1653591129458.png

 

 

 

@amitchandak ,

Inside the measure "min week start of month", what does [Month Year] represent exactly for a value?...202205 or something else? 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.