Forum Discussion
Advocate IIICalculating Month-To-Date and Week-to-Date
Hi Rsanjuan,
I think the formula for month and week won’t work here, and as you concerned, the formula can’t identify which year the month belongs to, also for the week.
For the Previous sales, the formula posted might not be the correct one, see the testing results, the formula used are all copied from the posted ones:
Which I think should be the correct one is the following:
SalesYTD =
TOTALYTD (
[TotalSales],
'Date'[Date]
)
And:
SalesLastYear =
CALCULATE([TotalSales], DATEADD(DATESYTD('Date'[Date]),-1,Year))
Those two formula need a date table to work with.
For month total and Week total, see:
"
Iso MTD :=
IF (
HASONEVALUE ( Dates[ISO Year] )
&& HASONEVALUE (Dates[ISO Month Number] ),
CALCULATE (
SUM ( Sales[Sales Amount] ),
FILTER (
ALL ( Dates ),
Dates[ISO Year] = VALUES ( Dates[ISO Year] )
&& Dates[ISO Month Number] = VALUES ( Dates[ISO Month Number] )
&& Dates[Date] <= MAX ( Dates[Date] )
)
),
BLANK ()
)
Iso WTD :=
IF (
HASONEVALUE ( Dates[ISO Year] )
&& HASONEVALUE (Dates[ISO Week Number] ),
CALCULATE (
SUM ( Sales[Sales Amount] ),
FILTER (
ALL ( Dates ),
Dates[ISO Year] = VALUES ( Dates[ISO Year] )
&& Dates[ISO Week Number] = VALUES ( Dates[ISO Week Number] )
&& Dates[Date] <= MAX ( Dates[Date] )
)
),
BLANK ()
)
"
Check details in the the article below:
Week-Based Time Intelligence in DAX
Before using the formula posted in the article, we need to create a date table containing the following columns:
Year, month and week number in a year.
New table function to create a datetable;
Datatable = calendar(MinDate, MaxDate);
Then add the following columns:
Year = year(Datetable[Date])
Month = month (Datetable[Date])
Weeknum = WeekNum(Datetable[Date])
After that, follow the formula mentioned in the blog to generate the YTD, MTD and WTD function.
If any further help needed, please post back.
Regards
I have 3 columns "Month" & "Achievement" & "Location" that I need to create 1 calculated column to get "Average Number" from "Achievement" column using all location but in every month separated
The average should be like the table below but I can't get the number with DAX.
Kindly any advice
| Month | Achievement | Location | Average |
| 1/1/2017 | 80 | 101 | 85 |
| 1/1/2017 | 90 | 102 | 85 |
| 2/1/2017 | 20 | 103 | 40 |
| 2/1/2017 | 60 | 105 | 40 |
Hi Mohamed,
I have a long shot solution for this.
1. Create a Date_Table with the following columns:
| Date | Week | Month | Quarter | Year | Month_Begin |
| 1/1/2017 | 1 | 1 | 1 | 2017 | Sunday, January 1, 2017 |
| 1/2/2017 | 1 | 1 | 1 | 2017 | Sunday, January 1, 2017 |
| 1/3/2017 | 1 | 1 | 1 | 2017 | Sunday, January 1, 2017 |
| … | … | … | … | … | … |
| 2/1/2017 | 5 | 2 | 1 | 2017 | Wednesday, February 1, 2017 |
| 2/2/2017 | 5 | 2 | 1 | 2017 | Wednesday, February 1, 2017 |
| 2/3/2017 | 5 | 2 | 1 | 2017 | Wednesday, February 1, 2017 |
For the Month_Begin column, create a calculated column using the following formula and make it a date format:
Month_Begin = 'Date_Table'[Month] & "-" & 'Date_Table'[Year]
2. Establish a relationship between the date from your achievement table to the Date of the Date_Table
3. Create your measure
Average = AVERAGE('Achivement'[Achievement])
4. Create a Matrix Table
Rows: 'Date_Table'[Month_Begin], 'Achievement'[Location]
Values: 'Achievement'[Average]
4. On your Matrix table, click on the double down arrow. You will now have your average for each Month_Begin as well as the break down for each location.