Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Need to show in a column the previous value for the dates shown in the iamge below. For example, the 5/24/2021 will show no data and 5/31/2021 will show the previous value which is 66,283,907. Dates are calculated as
Solved! Go to Solution.
Hi @UditJ ,
I suggest you to create a DimDate table to help your calculation.
DimDate =
VAR _STEP1 =
ADDCOLUMNS (
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"Week Start Date",
[Date] - WEEKDAY ( [Date], 2 ) + 1
)
VAR _STEP2 =
ADDCOLUMNS (
_STEP1,
"Year of Week", YEAR ( [Week Start Date] ),
"WEEKNUM", WEEKNUM ( [Week Start Date] ) - 1
)
RETURN
_STEP2
Measure:
Previous Week Impression =
VAR _PREVIOUWEEK =
MAXX (
FILTER (
ALL ( DimDate ),
DimDate[Week Start Date] < MAX ( DimDate[Week Start Date] )
),
[Week Start Date]
)
RETURN
CALCULATE (
SUM ( 'Reporting Dashboard_Social'[Impressions] ),
FILTER ( ALL ( DimDate ), DimDate[Week Start Date] = _PREVIOUWEEK )
)
Previous Year Week Impression =
CALCULATE (
SUM ( 'Reporting Dashboard_Social'[Impressions] ),
FILTER (
ALL ( DimDate ),
DimDate[Year of Week]
= MAX ( DimDate[Year of Week] ) - 1
&& DimDate[WEEKNUM] = MAX ( DimDate[WEEKNUM] )
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@UditJ It's essentially this pattern.
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
What if i want to show last years weeks values ? so a new column showing last year weeks values. Last year week can be as close as possible. Any suggestions ?
Hi @UditJ ,
I suggest you to create a DimDate table to help your calculation.
DimDate =
VAR _STEP1 =
ADDCOLUMNS (
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"Week Start Date",
[Date] - WEEKDAY ( [Date], 2 ) + 1
)
VAR _STEP2 =
ADDCOLUMNS (
_STEP1,
"Year of Week", YEAR ( [Week Start Date] ),
"WEEKNUM", WEEKNUM ( [Week Start Date] ) - 1
)
RETURN
_STEP2
Measure:
Previous Week Impression =
VAR _PREVIOUWEEK =
MAXX (
FILTER (
ALL ( DimDate ),
DimDate[Week Start Date] < MAX ( DimDate[Week Start Date] )
),
[Week Start Date]
)
RETURN
CALCULATE (
SUM ( 'Reporting Dashboard_Social'[Impressions] ),
FILTER ( ALL ( DimDate ), DimDate[Week Start Date] = _PREVIOUWEEK )
)
Previous Year Week Impression =
CALCULATE (
SUM ( 'Reporting Dashboard_Social'[Impressions] ),
FILTER (
ALL ( DimDate ),
DimDate[Year of Week]
= MAX ( DimDate[Year of Week] ) - 1
&& DimDate[WEEKNUM] = MAX ( DimDate[WEEKNUM] )
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |