Martin has attended:
Power BI Modelling, Visualisation and Publishing course
Month to Date & PY to date
Good Morning Jens,
Thank-you for your help with the integration - We've got that working now.
Another aspect I'm struggling with -
We want a MTD and PYMTD by Rep and Tier
I've got the MTD working.
Total Sales MTD =
TOTALMTD([Total Sales],'customerorders'[orderdate])
But my PYMTD works for an overall figure and Tier, but not when I add the reps in and I don't understand why.
MTD Prev Year
TOTALMTD([Total Sales],DATEADD('customerorders'[orderdate],-1,YEAR))
I'm probably missing something really obvious!
Martin
RE: Month to Date & PY to date
Good morning, Martin!
Thank you for the forum question.
I'm glad to hear the integration is working well. Let's tackle the issue with your PYMTD calculation.
The problem might be related to how the DATEADD function interacts with your data when you add the reps. To ensure that the calculation respects the context of each rep, you can use the SAMEPERIODLASTYEAR function instead of DATEADD. This function is often more reliable for year-over-year comparisons.
Here's how you can adjust your PYMTD calculation:
MTD Prev Year =
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR('customerorders'[orderdate])
)
This should give you the previous year's month-to-date sales by rep and tier. If you still encounter issues, it might be helpful to check if there are any filters or relationships in your data model that could be affecting the calculation.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Month to Date & PY to date
Thank-you Jens, but I'm still having random issues with this and I've attempted a few different work arounds and rather than giving me PYMTD its giving me total sales for all the data available using
MTD PY =
CALCULATE([Total Sales],
SAMEPERIODLASTYEAR(Date_Table[Date]))
and if I change Total Sales to Total Sales MTD (As below) it gives current month.
I've checked for filters but I don't believe that is causing problems as -
MTD Prev Year =
CALCULATE([Total Sales MTD],
PARALLELPERIOD('customerorders'[orderdate],-1,YEAR))
Does give the total figure for the Period I'm looking for (I've double checked it).
All are working with both the Rep and Product Tier now, though.
RE: Month to Date & PY to date
Hi Martin,
It sounds like you're on the right track, but there might be a few nuances causing the issue. Let's try to address them step by step.
Using SAMEPERIODLASTYEAR: The SAMEPERIODLASTYEAR function should work correctly, but it seems like it might be returning the total sales for the entire period instead of just the month-to-date. This can happen if the context isn't correctly set.
Try using the following formula to ensure the context is correctly applied:
MTD PY =
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR('customerorders'[orderdate]),
DATESMTD('customerorders'[orderdate])
)
This ensures that the calculation is limited to the month-to-date period of the previous year.
Using PARALLELPERIOD: The PARALLELPERIOD function can also be used, but it might need a bit of tweaking to ensure it works correctly with the month-to-date context.
MTD Prev Year =
CALCULATE(
[Total Sales MTD],
PARALLELPERIOD('customerorders'[orderdate], -1, YEAR)
)
Ensure that Total Sales MTD is correctly defined as:
Total Sales MTD =
TOTALMTD([Total Sales], 'customerorders'[orderdate])
Checking Filters and Relationships: Since you've mentioned that filters don't seem to be the issue, it's worth double-checking the relationships in your data model. Ensure that the Date_Table is correctly related to the customerorders table and that there are no inactive relationships that might be affecting the calculations.
Debugging with a Simple Measure: Sometimes, breaking down the problem with simpler measures can help identify the issue. Try creating a measure that simply calculates the previous year's sales without the month-to-date context to see if it returns the expected results:
Total Sales PY =
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR('customerorders'[orderdate])
)
If this works correctly, then the issue is likely with how the month-to-date context is being applied.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
