Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a week ending date column and starts revenue in my report.
I have to build a report which shows
How many starts last week compared to the previous week, previous month, previous year , like shown below
How many starts last month compared to the previous month, previous year
How many starts last year compared to the previous year
How many starts last week compared to the cumulative starts for the same period of the previous year
Can you please help me how to create the DAX for the above requirement as I have weekending column as date column.
Thanks & Regards,
Neelofar Shama.
Solved! Go to Solution.
@neelofarshama For this type of time intelligence a date table will make your life a bit easier 🙂 I have the last week, last month, and last year of the weekly amounts created for you in the attached PBIX. Once you have a date table, the measures can be created.
Date table I used, (Modeling --> New Table):
Date =
ADDCOLUMNS (
CALENDAR (
DATE ( YEAR ( MIN ( 'Table'[WEEK_ENDING] ) ), 1, 1 ),
DATE ( YEAR ( MAX ( 'Table'[WEEK_ENDING] ) ), 12, 31 )
),
"Month", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ),
"Year", DATE ( YEAR ( [Date] ), 1, 1 ),
"WeekOf",
[Date] - WEEKDAY ( [Date], 1 ) + 1,
"Monthly Week Number",
WEEKNUM ( [Date], 1 )
- WEEKNUM ( DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ), 1 ) + 1,
"Yearly Week Number", WEEKNUM ( [Date] )
)
and the measures:
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
Hi @neelofarshama ,
If you don't want to create a calendar table, you can try this:
Week Number = WEEKNUM(MAX('Table'[WEEK_ENDING]),2)
Month WeekNum =
VAR CurrentWeekEnding = MAX ( 'Table'[WEEK_ENDING] )
RETURN
WEEKNUM ( CurrentWeekEnding, 2 )
- WEEKNUM (
DATE ( YEAR ( CurrentWeekEnding ), MONTH ( CurrentWeekEnding ), 1 ),
2
) + 1
Last Week STARTS =
SUMX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[WEEK_ENDING]
= MAX ( 'Table'[WEEK_ENDING] ) - 7
),
[STARTS]
)
Last Month STARTS =
VAR CurrentMonthWeekNum = [Month WeekNum]
VAR CurrentWeekEnding =
MAX ( 'Table'[WEEK_ENDING] )
VAR CurrentYear =
YEAR ( CurrentWeekEnding )
VAR CurrentMonth =
MONTH ( CurrentWeekEnding )
RETURN
SWITCH (
CurrentMonth,
1,
SUMX (
FILTER (
ALLSELECTED ( 'Table' ),
YEAR ( 'Table'[WEEK_ENDING] ) = CurrentYear - 1
&& MONTH ( 'Table'[WEEK_ENDING] ) = 12
&& [Month WeekNum] = CurrentMonthWeekNum
),
[STARTS]
),
SUMX (
FILTER (
ALLSELECTED ( 'Table' ),
YEAR ( 'Table'[WEEK_ENDING] ) = CurrentYear
&& MONTH ( 'Table'[WEEK_ENDING] ) = CurrentMonth - 1
&& [Month WeekNum] = CurrentMonthWeekNum
),
[STARTS]
)
)
Last Year STARTS =
VAR CurrentYearWeekNum = [Week Number]
VAR CurrentWeekEnding =
MAX ( 'Table'[WEEK_ENDING] )
VAR CurrentYear =
YEAR ( CurrentWeekEnding )
VAR CurrentMonth =
MONTH ( CurrentWeekEnding )
RETURN
SUMX (
FILTER (
ALLSELECTED ( 'Table' ),
YEAR ( 'Table'[WEEK_ENDING] ) = CurrentYear-1
&& [Week Number] = CurrentYearWeekNum
),
[STARTS]
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @neelofarshama ,
If you don't want to create a calendar table, you can try this:
Week Number = WEEKNUM(MAX('Table'[WEEK_ENDING]),2)
Month WeekNum =
VAR CurrentWeekEnding = MAX ( 'Table'[WEEK_ENDING] )
RETURN
WEEKNUM ( CurrentWeekEnding, 2 )
- WEEKNUM (
DATE ( YEAR ( CurrentWeekEnding ), MONTH ( CurrentWeekEnding ), 1 ),
2
) + 1
Last Week STARTS =
SUMX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[WEEK_ENDING]
= MAX ( 'Table'[WEEK_ENDING] ) - 7
),
[STARTS]
)
Last Month STARTS =
VAR CurrentMonthWeekNum = [Month WeekNum]
VAR CurrentWeekEnding =
MAX ( 'Table'[WEEK_ENDING] )
VAR CurrentYear =
YEAR ( CurrentWeekEnding )
VAR CurrentMonth =
MONTH ( CurrentWeekEnding )
RETURN
SWITCH (
CurrentMonth,
1,
SUMX (
FILTER (
ALLSELECTED ( 'Table' ),
YEAR ( 'Table'[WEEK_ENDING] ) = CurrentYear - 1
&& MONTH ( 'Table'[WEEK_ENDING] ) = 12
&& [Month WeekNum] = CurrentMonthWeekNum
),
[STARTS]
),
SUMX (
FILTER (
ALLSELECTED ( 'Table' ),
YEAR ( 'Table'[WEEK_ENDING] ) = CurrentYear
&& MONTH ( 'Table'[WEEK_ENDING] ) = CurrentMonth - 1
&& [Month WeekNum] = CurrentMonthWeekNum
),
[STARTS]
)
)
Last Year STARTS =
VAR CurrentYearWeekNum = [Week Number]
VAR CurrentWeekEnding =
MAX ( 'Table'[WEEK_ENDING] )
VAR CurrentYear =
YEAR ( CurrentWeekEnding )
VAR CurrentMonth =
MONTH ( CurrentWeekEnding )
RETURN
SUMX (
FILTER (
ALLSELECTED ( 'Table' ),
YEAR ( 'Table'[WEEK_ENDING] ) = CurrentYear-1
&& [Week Number] = CurrentYearWeekNum
),
[STARTS]
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@neelofarshama For this type of time intelligence a date table will make your life a bit easier 🙂 I have the last week, last month, and last year of the weekly amounts created for you in the attached PBIX. Once you have a date table, the measures can be created.
Date table I used, (Modeling --> New Table):
Date =
ADDCOLUMNS (
CALENDAR (
DATE ( YEAR ( MIN ( 'Table'[WEEK_ENDING] ) ), 1, 1 ),
DATE ( YEAR ( MAX ( 'Table'[WEEK_ENDING] ) ), 12, 31 )
),
"Month", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ),
"Year", DATE ( YEAR ( [Date] ), 1, 1 ),
"WeekOf",
[Date] - WEEKDAY ( [Date], 1 ) + 1,
"Monthly Week Number",
WEEKNUM ( [Date], 1 )
- WEEKNUM ( DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ), 1 ) + 1,
"Yearly Week Number", WEEKNUM ( [Date] )
)
and the measures:
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |