Forum Discussion
MTD and Last Year MTD - DAX - DirectQuery
I am using this formula to calculate MTD transaction counts for prior year:
PY Trx = CALCULATE(sum(Daily[Trx]), FILTER(
Daily, Daily[Year]=YEAR(max(Daily[Date]))-1 && Daily[Month]=MONTH(MAX(Daily[Date])) && Daily[Day Modified]<=day(MAX(Daily[Date]))))
- [Year]=Year(Daily[Date])
- [Month]=Month(Daily[Date])
- Modified Date =
IF(Year([Date])=YEAR(today()),[Date],
IF(Year([Date])=year(today())-1,[Date]+364,
IF(Year([Date])=year(today())-2,[Date]+728,[Date]))) - Day Modified= Day(Daily[Modified Date])
I use Matrix to show the results:
- row: salesreps
- columns: vehicle types
- values: PY Trx
ISSUE 1: The total for columns and rows are correct, however it does not allocate the correct number of each individual vehicles types sold to each individual salesrep.
ISSUE 2: It does not work when I publish it online (Data Gateway is already established. The other parts of the report are working currectly.)
LIMITATIONS: I need to be able to change date range and navigate through different time periods (This is a DirectQuery, and the table is getting updated daily).
I appreciate any help and suggestion!
4 Replies
- Ashish_Mathur
Super User
Hi,
Create a Calendar Table and build a relationship from the Date column of the Daily Table to the Date column of the Calendar Table. Create a slicer from the Date column of the Calendar table and select any date there. Write these measures
Sales MTD = CALCULATE(sum(Daily[Trx]),MTD(Calendar[Date]))
Sales MTD LY = CALCULATE([Sales MTD],SAMEPERIODLASTYEAR(Calendar[Date]))
Hi Ashish,
Thank you for your response!
I need to align current year weekdays with those of last year, not sure how I can achieve this by using SAMEPERIODLASTYEAR formula.
Also, this works when I manually filter dates, however this table is getting updated on daily basis. Any recommendation on any alternative approach?
Much obliged!
- Ashish_Mathur
Hi,
I can help you only if you share some data and the expected result.
